1

I have 247 sheets in an Excel file and I need to copy the rows from each sheet and paste in one "Master" sheet. Each sheet has between 100 and 400 rows. For some reason the code I have is omitting most of the sheets and seems to work and copy only few sheets.

Sub CleanV2()
Dim ws As Worksheet
Dim Lastrow As Long
Dim finalRow As Long
Dim H As Integer

Application.ScreenUpdating = True

'''''''''Paste to Analysis'''''
    
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "ImportCommand" And ws.Name <> "Analysis" And ws.Name <> "Cities" Then
ws.Select

Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:H" & Lastrow).Select
Selection.Copy
 Sheets("Analysis").Select
 
 
  finalRow = Cells(Rows.Count, 1).End(xlUp).Row
 
H = finalRow + 1
 'Sheets("MasterData").Select
Sheets("Analysis").Range("$A$" & H).Select
On Error Resume Next
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
On Error Resume Next

End If
Next
''''''Finish paste'''''

End Sub

Any help why why code is not copying and pasting each sheet. It seems code is copying each sheet but the paste is not working as needed.

Thank you

user3618585
  • 39
  • 1
  • 1
  • 9
  • 1
    I'd start with removing the `On Error Resume Next`, [avoiding Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba), and [transferring the value directly](https://stackoverflow.com/questions/51528000/vba-paste-as-values-how-to). – BigBen Oct 21 '21 at 13:17
  • Hello - Can you please clarify "avoiding Select, and transferring the value directly" ? – user3618585 Oct 21 '21 at 13:19
  • Did you click the hyperlinks? Basically, avoid using `Select` and `Selection`, and transfer the `.Value` of the range directly instead of using `.PasteSpecial Paste:=xlPasteValues`. – BigBen Oct 21 '21 at 13:20
  • Oh havent checked the hyperlinks. Let me see./ – user3618585 Oct 21 '21 at 13:23
  • I started with removing "On Error Resume Next" the code runs okay but then stops with this error: "Overflow" – user3618585 Oct 21 '21 at 13:24
  • 1
    `Dim H As Long` – BigBen Oct 21 '21 at 13:27

0 Answers0