UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> VBA Copy A Range Instead Of A Row, Office 2007    
 
   
Dan Dungan
post Jun 16 2017, 04:57 PM
Post#1



Posts: 242
Joined: 20-July 10
From: chatsworth, ca


Hi UA,

I have a spreadsheets that show which jobs, part numbers, quantities and other job data by Department. Each department is a separate sheet. I have this code for each department that copies a row from the data to the department report.

Due to new requirements, I don't want to copy the whole row, just column A through L.

How could i change this code to that end?

CODE
Sub CopyMILL()
    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet

    ' Change worksheet designations as needed
    Set Source = ActiveWorkbook.Worksheets("LastItemData")
    Set Target = ActiveWorkbook.Worksheets("MILL")
    Target.Range("a4:N2000").Clear
    j = 4     ' Start copying to row 4 in target sheet
    For Each c In Source.Range("L3:L2000")   ' Do 2000 rows
        If c = "MILL" Then
           Source.Rows(c.Row).Copy Target.Rows(j)
           j = j + 1
        End If
    Next c
End Sub

--------------------
Dan
Go to the top of the page
 
Antialias401
post Jun 16 2017, 10:44 PM
Post#2



Posts: 46
Joined: 23-May 14



Backup your original code and replace it with this:

CODE
Sub CopyMILL()
    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet

    ' Change worksheet designations as needed
    Set Source = ActiveWorkbook.Worksheets("LastItemData")
    Set Target = ActiveWorkbook.Worksheets("MILL")
    Target.Range("a4:L2000").Clear
    j = 4     ' Start copying to row 4 in target sheet
    For Each c In Source.Range("L3:L2000")   ' Do 2000 rows
        If c = "MILL" Then
           Source.Range("A" & c.Row & ":L" & c.Row).Copy Target.Rows(j) 'Only copy columns A through L
           j = j + 1
        End If
    Next c
End Sub


This code is modified to only copy A through L instead of the entire row.
This post has been edited by Antialias401: Jun 16 2017, 10:47 PM
Go to the top of the page
 
Dan Dungan
post Jun 19 2017, 12:21 PM
Post#3



Posts: 242
Joined: 20-July 10
From: chatsworth, ca


Perfect. Thank you Anti

--------------------
Dan
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    20th September 2017 - 06:14 AM