Copy specific columns of 2 sheets from Workbook A to 1 Summary sheet of workbook B

[coexcelnewbie]

Sub Rectangle1_Click()


Dim lr As Long
Here is my code. However, it only copies the columns from the 1st sheet. How can I add the data from the 2nd sheet below the copied data from the 1st sheet into the summary (RAID)?


Workbooks("Source").Activate


lr = Cells(Rows.Count, 3).End(xlUp).Row


Range("B3:B" & lr).Copy Workbooks("Report Destination").Sheets("RAID").Range("B3")
Range("E3:E" & lr).Copy Workbooks("Report Destination").Sheets("RAID").Range("C3")
Range("T3:T" & lr).Copy Workbooks("Report Destination").Sheets("RAID").Range("D3")
Range("H3:I3" & lr).Copy Workbooks("Report Destination").Sheets("RAID").Range("E3")
Range("AC3:AC" & lr).Copy Workbooks("Report Destination").Sheets("RAID").Range("G3")




End Sub

 

[JLGWhiz]

Sub copyStuff()
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Workbooks("Source").Sheets("Sheet1") 'Substitute your 1st source sheet name here
Set sh2 = Workbooks("Source").Sheets("Sheet2") 'Substitute your 2nd source sheet nsme here
With sh1
   lr = Cells(Rows.Count, 3).End(xlUp).Row
   .Range("B3:B" & lr).Copy Workbooks("Report Destination").Sheets("RAID").Range("B3")
   .Range("E3:E" & lr).Copy Workbooks("Report Destination").Sheets("RAID").Range("C3")
   .Range("T3:T" & lr).Copy Workbooks("Report Destination").Sheets("RAID").Range("D3")
   .Range("H3:I3" & lr).Copy Workbooks("Report Destination").Sheets("RAID").Range("E3")
   .Range("AC3:AC" & lr).Copy Workbooks("Report Destination").Sheets("RAID").Range("G3")
End With
With sh2
   lr = Cells(Rows.Count, 3).End(xlUp).Row 'Validate column reference is 3 for 2nd sheet
   [COLOR=#daa520]'your range to copy here.[/COLOR]Copy Workbooks("Report Destination").Sheets("RAID").Cells(Rows.Count, [COLOR=#b22222]"B[/COLOR]").End(xlUp)(2)
End With
End Sub

Change the column reference to reflect which column you want to post to.
The commented line would be repeated for each range to copy similar to your original code. The syntax for posting the values will look for the next avaliable row within that column. The source range references will need a period (.) in front of them to tie them to the 'With' statement.

 

[coexcelnewbie]

Sub copyStuff()
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Workbooks("Source").Sheets("Sheet1") 'Substitute your 1st source sheet name here
Set sh2 = Workbooks("Source").Sheets("Sheet2") 'Substitute your 2nd source sheet nsme here
With sh1
   lr = Cells(Rows.Count, 3).End(xlUp).Row
   .Range("B3:B" & lr).Copy Workbooks("Report Destination").Sheets("RAID").Range("B3")
   .Range("E3:E" & lr).Copy Workbooks("Report Destination").Sheets("RAID").Range("C3")
   .Range("T3:T" & lr).Copy Workbooks("Report Destination").Sheets("RAID").Range("D3")
   .Range("H3:I3" & lr).Copy Workbooks("Report Destination").Sheets("RAID").Range("E3")
   .Range("AC3:AC" & lr).Copy Workbooks("Report Destination").Sheets("RAID").Range("G3")
End With
With sh2
   lr = Cells(Rows.Count, 3).End(xlUp).Row 'Validate column reference is 3 for 2nd sheet
   [COLOR=#daa520]'your range to copy here.[/COLOR]Copy Workbooks("Report Destination").Sheets("RAID").Cells(Rows.Count, [COLOR=#b22222]"B[/COLOR]").End(xlUp)(2)
End With
End Sub

Change the column reference to reflect which column you want to post to.
The commented line would be repeated for each range to copy similar to your original code. The syntax for posting the values will look for the next avaliable row within that column. The source range references will need a period (.) in front of them to tie them to the 'With' statement.

There are no errors but it's not copy pasting properly..

With sh2
lr = Cells(Rows.Count, 3).End(xlUp).Row 'Validate column reference is 3 for 2nd sheet
'your range to copy here.Copy Workbooks("Report Destination").Sheets("RAID").Cells(Rows.Count, "B").End(xlUp)(2)
.Range("B3:B" & lr).Copy Workbooks("Report Destination").Sheets("RAID").Cells(Rows.Count, "B").End(xlUp)(2)
.Range("E3:E" & lr).Copy Workbooks("Report Destination").Sheets("RAID").Cells(Rows.Count, "C").End(xlUp)(2)
.Range("T3:T" & lr).Copy Workbooks("Report Destination").Sheets("RAID").Cells(Rows.Count, "D").End(xlUp)(2)
.Range("H3:I3" & lr).Copy Workbooks("Report Destination").Sheets("RAID").Cells(Rows.Count, "E").End(xlUp)(2)
.Range("AC3:AC" & lr).Copy Workbooks("Report Destination").Sheets("RAID").Cells(Rows.Count, "G").End(xlUp)(2)



End With

 

[JLGWhiz]

What do you mean by 'not posting properly'? Wrong sheet? Wrong column? Which one? Wrong row? What? Please be specific when describing code results.

 

[coexcelnewbie]

What do you mean by 'not posting properly'? Wrong sheet? Wrong column? Which one? Wrong row? What? Please be specific when describing code results.

I wanted to copy filtered columns for both sheets. However, it's copying the selected columns ignoring the filter and It's also only copying the first sheet.

Sub Rectangle1_Click()
Dim lr As Long


Workbooks("Source").Activate
Set sh1 = Workbooks("Source").Sheets("Risks") 'Substitute your 2nd source sheet nsme here
Set sh2 = Workbooks("Source").Sheets("Issues") 'Substitute your 2nd source sheet nsme here


With sh1
lr = Cells(Rows.Count, 3).End(xlUp).Row


Range("B3:B" & lr).Copy Workbooks("Report - Email").Sheets("RAID").Range("B3")
Range("E3:E" & lr).Copy Workbooks("Report - Email").Sheets("RAID").Range("C3")
Range("T3:T" & lr).Copy Workbooks("Report - Email").Sheets("RAID").Range("D3")
Range("H3:I3" & lr).Copy Workbooks("Report - Email").Sheets("RAID").Range("E3")
Range("AC3:AC" & lr).Copy Workbooks("Report - Email").Sheets("RAID").Range("G3")
End With
With sh2
lr = Cells(Rows.Count, 3).End(xlUp).Row 'Validate column reference is 3 for 2nd sheet
'your range to copy here.Copy Workbooks("Report Destination").Sheets("RAID").Cells(Rows.Count, "B").End(xlUp)(2)
Range("B3:B" & lr).Copy Workbooks("Report - Email").Sheets("RAID").Cells(Rows.Count, "B").End(xlUp)(2)
Range("E3:E" & lr).Copy Workbooks("Report - Email").Sheets("RAID").Cells(Rows.Count, "C").End(xlUp)(2)
Range("T3:T" & lr).Copy Workbooks("Report - Email").Sheets("RAID").Cells(Rows.Count, "D").End(xlUp)(2)
Range("H3:I3" & lr).Copy Workbooks(" Report - Email").Sheets("RAID").Cells(Rows.Count, "E").End(xlUp)(2)
Range("AC3:AC" & lr).Copy Workbooks(" Report - Email").Sheets("RAID").Cells(Rows.Count, "G").End(xlUp)(2)


End With


End Sub

 

[JLGWhiz]

The OP says nothing about the data being in a filter. To copy filtered data with VBA you need to use the 'SpecialCells' function.

Range("B3:B" & lr).[COLOR=#FF0000]SpecialCells(xlCellTypeVisible)[/COLOR].Copy Workbooks("Report - Email").Sheets("RAID").Range("B3")