VBA PasteSpecial - cant paste values from columns

[mysticmario]

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?

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

 

[mysticmario]

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?

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

Made some changes to the 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, "Faktury") Then
        lr = Sheets("PANEL PODLICZEŃ").Cells(Rows.Count, "D").End(xlUp).Row
        lr2 = Sheets("PANEL PODLICZEŃ").Cells(Rows.Count, "E").End(xlUp).Row
        wsSheet.Activate
ActiveSheet.Range("D:D").Select
        Selection.Copy
        Sheets("PANEL PODLICZEŃ").Cells(lr + 1, "D").PasteSpecial Paste:=xlPasteValues
ActiveSheet.Range("E:E").Select
        Selection.Copy
        Sheets("PANEL PODLICZEŃ").Cells(lr2 + 1, "E").PasteSpecial Paste:=xlPasteValues
        End If
Next wsSheet

End Sub

Also here's the error :

 

[kokano90]

ActiveSheet.Range("D:D").Select

name activesheet??

 

[mysticmario]

ActiveSheet.Range("D:D").Select

name activesheet??

wsSheet holds the name which would be any sheet that has "Faktury" in it's name
after that I activate wsSheet, therefore activesheet is wsSheet by default

 

[kokano90]

wsSheet holds the name which would be any sheet that has "Faktury" in it's name
after that I activate wsSheet, therefore activesheet is wsSheet by default

the range ("D: D") is 1000000 rows. When pasting into .cells( lr+1) there will be a missing row leading to an error

 

[mysticmario]

the range ("D: D") is 1000000 rows. When pasting into .cells( lr+1) there will be a missing row leading to an error

So what would be the best way to copy columns D and E from each sheet with "Faktury" in it's name to a specific sheet.
Originally i wanted to do excel sumsifs formula but it's really hard to do for so many sheets.
So i decided to copy all these values to one sheet and then sumifs for them
I need formula like this: =SUMIFS(E:E;D:D;">="&M5;D:D;"<="&M6)
That extends to all sheets

 

[kokano90]

So what would be the best way to copy columns D and E from each sheet with "Faktury" in it's name to a specific sheet.
Originally i wanted to do excel sumsifs formula but it's really hard to do for so many sheets.
So i decided to copy all these values to one sheet and then sumifs for them
I need formula like this: =SUMIFS(E:E;D:D;">="&M5;D:D;"<="&M6)
That extends to all sheets

Can you attach the file to google drive and pull the link here?

 

[mysticmario]

Can you attach the file to google drive and pull the link here?

I will send it to you in pm,
However it is large excel in Polish language mostly.

 

[mysticmario]

Can't send a PM unfortunately so here's the link:
[Deleted link]

 

[mysticmario]

let me know when u get it so I can delete it form here, it's not a file that should be shared publicly