Make Variable Range in VBA

[Jholerjo]

Hi Excel Gurus,

I want to change this hard coding macro with variable:

Sub test()
Range("I2").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ChDir _"C:\Documents and Settings\Jeffry Husman\My Documents\Excel
Workbook\Macro Exercise"
ActiveWorkbook.SaveAs Filename:= _"C:\Documents and Settings\Jeffry
Husman\My Documents\Excel Workbook\Macro Exercise\jeff1.xls" _,
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close

I want to be able to make the range add automatically from range (I2) up to
Range (I500) and save the file name with jeff1 up to jeff500.

Thanks in advance

 

[Ian]

Judging by the code this was the result of recording the macro. To modify
the code you need to add a variable for the row number. I'm assuming you
need to start at row 1 and save this as Jeff1 and so on.

The first line of code should read For r = 1 to 500
The last line of code should read Next
These will ensure that the code between runs 500 times, incrementing r each
time

Change Range("I2").Select to Range("I" & r).Select

Delete these lines:
ChDir _
"C:\Documents and Settings\Jeffry Husman\My Documents\Excel
Workbook\Macro Exercise"
You don't need them as you are specifying the full path\filename in the
SaveAs statement

Change the filename from "jeff1.xls" to "jeff" & r ".xls" as below

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Jeffry Husman\My Documents\Excel
Workbook\Macro Exercise\jeff" & r &".xls"

Delete these lines:
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
These are the default settings which Excel will use unless you specify
anything else.

I want to be able to make the range add automatically from range (I2) up
to
Range (I500) and save the file name with jeff1 up to jeff500.

With the example you've given, you would only get 499 files, not 500 (if the
first is jeff1 from I2, the last will be jeff 499 from I500)

Hope this helps

Ian

 

[Jholerjo]

I'm using recording macro indeed. I have very minimal VBE experience. I am
revising the coding to:

Sub test()

For r = 1 To 10
Range("I2").Select
Change Range("I2").Select To Range("I" & r).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Jeffry Husman\My Documents\Excel
Workbook\Macro Exercise\jeff" & r & ".xls"

ActiveWindow.Close

Next
End Sub

When try to run it, it says compile error on line Change Range("I2"). Select
to Range("I" & r).Select.

Please advise

 

[Ian]

Sorry, I was explaining what you should do, rather than sending you the code
verbatim.
Try this:

Sub test()
For r = 1 To 10
Range("I" & r).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Jeffry Husman\My Documents\Excel_
Workbook\Macro Exercise\jeff" & r & ".xls"
ActiveWindow.Close
Next
End Sub

FYI. The underscore at the end of a line enables you to keep the lines to a
manageable length. In the code above, ActiveWorkbook to ".xls" is all
treated as one line of code.

The result of running this code should be 10 separate workbooks, each with a
value in A1. The first workbook will have the value from cell I1 of your
source workbook and the last from cell I10 of the same book. I assume this
is what you need.

Hope this helps.

Ian

 

[Jholerjo]

It works perfectly. Thanks heaps

Regards
Jeff

Sorry, I was explaining what you should do, rather than sending you the code
verbatim.
Try this:

Sub test()
For r = 1 To 10
Range("I" & r).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Jeffry Husman\My Documents\Excel_
Workbook\Macro Exercise\jeff" & r & ".xls"
ActiveWindow.Close
Next
End Sub

FYI. The underscore at the end of a line enables you to keep the lines to a
manageable length. In the code above, ActiveWorkbook to ".xls" is all
treated as one line of code.

The result of running this code should be 10 separate workbooks, each with a
value in A1. The first workbook will have the value from cell I1 of your
source workbook and the last from cell I10 of the same book. I assume this
is what you need.

Hope this helps.

Ian

 

[Jholerjo]

Ian,

If I want to save the file name not with jeff" & r & "but with the value of
column I, how to I write the code?
I1:Excel
I2:Gurus
I3:etc

file name will be Excel, Gurus, etc. instead of jeff1, jeff2, jeff3.

Many Thanks
Jeff

 

[Ian]

Replace
Exercise\jeff" & r & ".xls"
with
Exercise\" & Range("I" & r).Value & ".xls"

or you could add a line earlier in the code to assign another variable

eg f = Range("I" & r).Value

then change the filename line to
Exercise\" & f & ".xls"

Ian