csucsii
03-21-2016, 08:21 PM
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.)
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.)