Range Copy/Paste Loop for Multiple Sheets to Multiple Sheets

[csucsii]

Greetings to you all,

I'm working on a econometrics project where I'm analyzing certain independent variables effect on government yields. However, from the collected thousands of variables I only need a few hundred, and to reduce the file size, I would like to copy these to a single Excel workbook with multiple sheets.


So with a source file with 57 sheets, I was trying to create a new workbook with 57 sheets but much less columns with something like this:

Sub CopyCatLoop()


Dim x As Integer
Dim WS As Worksheet


For Each WS In Workbooks("Country data.xlsm").Worksheets


Workbooks("CAT1.xls").Sheets.Add(After:=Workbooks("CAT1.xls").Sheets(Workbooks("CAT1.xls").Sheets.Count)).Name = x

WS.Range("A1:A1000").Copy Workbooks("CAT1.xls").Sheets(x).Range("A1:A1000")

//Continuing with all needed ranges here

x = x + 1


Next


End Sub


However, the Range.Copy command seems to fail every time (same for Range.Select and Selection.Copy). I'm guessing something is wrong with WS set up, but I couldn't figure out what and I'm afraid the next issue will be the x=x+1 command row (Next x gets rejected as invalid Next variable).

I really appreciate any help as I'm complete VBA newbie (I mean, completely, I started a day ago). Forgive me for my nonsense questions.

(If someone can point out how I can improve the paste/ copy destination part of the command, I'd be extra grateful. Meaning: how to add the specific columns/ranges copied simply just to the next empty column without specifying the exact destination range/column.)

[Jan Karel Pieterse]

You could turn things around and delete columns you don't need after making a copy of the entire workbook perhaps?

[csucsii]

Yes, indeed, but I was hoping that extracting a few variables would be easier than locating and deleting a few thousand.

[GTO]

Greetings to you all,

...However, the Range.Copy command seems to fail every time (same for Range.Select and Selection.Copy). I'm guessing something is wrong with WS set up, but I couldn't figure out what and I'm afraid the next issue will be the x=x+1 command row (Next x gets rejected as invalid Next variable)...


Greetings,

For a start, this line...

WS.Range("A1:A1000").Copy Workbooks("CAT1.xls").Sheets(x).Range("A1:A1000")

...will fail, because VBA thinks you are supplying the sheet's index number and as x starts out life as 0, it goes Kaboom... Try:

WS.Range("A1:A1000").Copy Workbooks("CAT1.xls").Sheets(CStr(x)).Range("A1")

This let's VBA know we are referring to the tab named "0".

Hope that helps,

Mark

[Jan Karel Pieterse]

In addition, you could copy all ranges in one fell swoop:


WS.Range("A:A,B:B,D:D,F:F,I:I,Z:Z").Copy Workbooks("CAT1.xls").Sheets(CStr(x)).Range("A1")
Control:select the columns to copy, then go to the VBA editor, press control+G and enter this line:
?Selection.Address
and press enter to get the right address string to put in my example.

[csucsii]

Thank you, Mark and Jan!
Both of yours answer was very helpful, but I ended up relying on the fact that both my source and target workbooks use the same sheet names, so:


Sub CopyCatLoop()

Dim Source As Workbook
Dim Target As Workbook
Dim ws As Worksheet

Set Source = Workbooks("Country data.xlsm")
Set Target = Workbooks("CAT1.xls")

For Each ws In Source.Worksheets

If Not ws.Name = "Countries" Then

ws.Range("A1:A1000").Copy TargetBook.Worksheets(ws.Name).Cells(1, 1)

''Rest of ranges

End If

Next ws

End Sub

[GTO]

I am glad that we were helpful and thank you for the feedback. Happy coding:thumb