mysticmario
Active Member
- Joined
- Nov 10, 2021
- Messages
- 323
- Office Version
- 365
- Platform
- Windows
Hi good people of mrexcel board
Trying to go through all the sheets select colums D and E and paste their value to another sheet.
Initially i wanted to do it without activating the sheets since I have 90+ sheets but so far I cant even make it work with this code.
The problem is with lr and paste values
can someone help me to fix it?
Trying to go through all the sheets select colums D and E and paste their value to another sheet.
Initially i wanted to do it without activating the sheets since I have 90+ sheets but so far I cant even make it work with this code.
The problem is with lr and paste values
can someone help me to fix it?
VBA Code:
Private Sub CommandButton1_Click()
Dim lr As Long
Dim lr2 As Long
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
If InStr(wsSheet.Name, "Invoices") Then
lr = Sheets("SUMMARY MENU").Cells(Rows.Count, "D").End(xlUp).Row
wsSheet.Activate
ActiveSheet.Range("D:D").Select
Selection.Copy
Sheets("SUMMARY MENU").Cells(lr + 1, "D").PasteSpecial Paste:=xlPasteValues
ActiveSheet.Range("E:E").Select
Selection.Copy
Sheets("SUMMARY MENU").Cells(lr2 + 1, "E").PasteSpecial Paste:=xlPasteValues
End If
Next wsSheet
End Sub