0

So I basically have this report A. I want to copy all of report B and paste it at the end of report A but it gave me this duplicate dim error and I dont know how to differentiate it. I realize the duplicates but I want the vba program to read my report A excel file ( If ws.Name Like "A*" Then ws.Activate) and activate it but ignore the numbers after A because the file name keeps changing. If theres another way to do that. That would be amazing help I've been trying to figure out all day.

Sub CopyBdata_into_Adata()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name Like "B*" Then
ws.Activate   
Exit Sub    
End If

Next ws
Rows("1:1").Select    
Selection.Delete Shift:=xlUp    
Range("A2:BG3").Select    
Range(Selection, Selection.End(xlDown)).Select    
Selection.Copy 

Dim ws As Worksheet    
For Each ws In Worksheets    
If ws.Name Like "A*" Then    
ws.Activate    
Exit Sub    
End If    
Next ws

ActiveWindow.ScrollRow = 20    
ActiveWindow.ScrollRow = 120    
ActiveWindow.ScrollRow = 259    
ActiveWindow.ScrollRow = 318    
ActiveWindow.ScrollRow = 1251    
ActiveWindow.ScrollRow = 1867    
ActiveWindow.ScrollRow = 2462    
ActiveWindow.ScrollRow = 4348    
ActiveWindow.ScrollRow = 5162    
ActiveWindow.ScrollRow = 6571    
ActiveWindow.ScrollRow = 6948    
ActiveWindow.ScrollRow = 7604    
ActiveWindow.ScrollRow = 7921    
ActiveWindow.ScrollRow = 8080    
ActiveWindow.ScrollRow = 8437    
ActiveWindow.ScrollRow = 8576    
ActiveWindow.ScrollRow = 9092    
ActiveWindow.ScrollRow = 9211    
ActiveWindow.ScrollRow = 9648    
ActiveWindow.ScrollRow = 9886    
ActiveWindow.ScrollRow = 10244    
ActiveWindow.ScrollRow = 10561    
ActiveWindow.ScrollRow = 11177    
ActiveWindow.ScrollRow = 11514    
ActiveWindow.ScrollRow = 12090    
ActiveWindow.ScrollRow = 12308    
ActiveWindow.ScrollRow = 12487    
ActiveWindow.ScrollRow = 13122    
ActiveWindow.ScrollRow = 13241    
ActiveWindow.ScrollRow = 13579    
ActiveWindow.ScrollRow = 13599    
ActiveWindow.ScrollRow = 13757    
ActiveWindow.ScrollRow = 13797    
ActiveWindow.ScrollRow = 13817    
ActiveWindow.ScrollRow = 13936    
ActiveWindow.ScrollRow = 13996    
ActiveWindow.ScrollRow = 14015    
ActiveWindow.ScrollRow = 14174    
ActiveWindow.ScrollRow = 14194    
ActiveWindow.ScrollRow = 14274    
ActiveWindow.ScrollRow = 14393    
ActiveWindow.ScrollRow = 14512    
ActiveWindow.ScrollRow = 14532    
ActiveWindow.ScrollRow = 14690    
ActiveWindow.ScrollRow = 14710    
ActiveWindow.ScrollRow = 14750    
ActiveWindow.ScrollRow = 14810    
ActiveWindow.ScrollRow = 14829    
ActiveWindow.ScrollRow = 14849    
ActiveWindow.ScrollRow = 14869    
ActiveWindow.ScrollRow = 14909    
ActiveWindow.ScrollRow = 14929    
ActiveWindow.ScrollRow = 14949    
ActiveWindow.SmallScroll Down:=3

Range("A14988").Select    
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _    
:=False, Transpose:=False

Windows("Report_Instructions_and_macros_Test1.xlsm").Activate

End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Amy White
  • 1
  • 1
  • 3
    You do not need to Dim the same variable twice. once it is declared it will be that type. You can replace the value or re set the object at anytime. remove the extra Dim line. – Scott Craner Jul 02 '19 at 22:26
  • 3
    Now that being said see this question/answers about not using `.Select` and `.Activate`: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba And remove all the `ActiveWindow.ScrollRow` lines, they are not needed and are only remnants of the macro recorder. – Scott Craner Jul 02 '19 at 22:27
  • Welcome to SO! Please take the [tour] and read [ask]! – ComputerVersteher Jul 02 '19 at 22:30

1 Answers1

0

Just remove the second

dim ws as Worksheet

statement. For a loop control variable in vba, you only need to dim it once within a sub or function.

Programnik
  • 1,449
  • 1
  • 9
  • 13