PDA

View Full Version : HELP... How to copy paste tabs content to another excel in the same order of tabs



kding0804
03-18-2015, 07:30 PM
I have been able to copy cells from different tabs of different excels, and paste into one active workbooks via VBA. But the current problem is more nasty.

The assignment is to copy a certain area of the 1st tab in an excel, and paste into the 1st tab in another excel, and do the same thing for tab#2, tab#3... up to tab #100. That means I will have to match the content b/t old and new excels for every tab.

We were trying to change the destination to 100 word files, but that's an ideal case... not sure if this is applicable.

I'm trying to make modifications on the old one to realize the new assignment.


Sub LoopThroughDirectory()
Dim Myfile As String
Dim erow
Dim i As Integer
Myfile = Dir("C:\officialdata_new\")


Do While Len(Myfile) > 0
If Myfile = "z2004code.xlsx" Then
Exit Sub
End If


i = 2
Do While i < 9
Workbooks.Open (ThisWorkbook.Path & "\" & Myfile)
Worksheets(i).Select
Range("A2:c8").Copy
ActiveWorkbook.Close


erow = Sheet7.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("sheet7").Range(Cells(erow, 1), Cells(erow, 3))


i = i + 1
Loop


Myfile = Dir


Loop


End Sub



Appreciated if anyone could help me. I will elaborate the problem if necessary.
Many thanks!!