VBA: Dynamic Workbook Name Reference

ragnar12

Board Regular
Joined
May 1, 2013
Messages
113
I am using:

Code:
Dim Aname As String
Aname = ActiveWorkbook.ActiveSheet.Range("D2").Value & "Pricing"
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=Aname & ".xls"

to create a new workbook. I need to copy 2 columns from the original workbook/worksheet to the one being created though. I can get the the original easy enough, but then getting back to the newly created book is proving difficult. I have considered making it save as a consistent name to solve this problem, but wanted to check here if anyone had any thoughts. Any help is greatly appreciated, thanks!

(the 2 columns being copied are filtered to what is needed and I have used the below to copy it, yes I know that is 3 rows but I couldnt get it to copy just E and G at the same time to the lastrow without including F)

Code:
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    
    With .Range("E2:G" & LastRow)
    .Select.copy
    End With
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,066
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
You need to add a new variable for the workbook being added like this

Code:
    Dim Aname As String
    Dim wkb As Workbook
    Aname = ActiveWorkbook.ActiveSheet.Range("D2").Value & "Pricing"
    Set wkb = Workbooks.Add
    ActiveWorkbook.SaveAs Filename:=Aname & ".xls"
    wkb.worksheets(1).name = "Ajasco" 'An example of referencing the new workbook
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,066
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
For the ranges that are non-contiguous, you could use
Code:
Union(Range("E1:E" & lastrow), Range("G1:G" & lastrow)).Copy
to copy just column E and G
 

ragnar12

Board Regular
Joined
May 1, 2013
Messages
113
Thanks momentman! That worked perfectly. I can't believe I forgot about the Union function I just read an article about that the other day, good call.
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,066
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

You are welcome
 

ragnar12

Board Regular
Joined
May 1, 2013
Messages
113
I am trying to reuse some code that is already in use, but I need to combine it with this new workbook that is created.

Right now its referenced as:

Code:
Excel.Workbooks("PRICING.xls").Sheets("Filter").Cells(TCtr, 1)

But the workbook now has the dynamic name using your code (with my new sheet names):

Code:
Dim Aname As String
    Dim wkb As Workbook
    Aname = ActiveWorkbook.ActiveSheet.Range("D2").Value & "Pricing"
    Set wkb = Workbooks.Add
    ActiveWorkbook.SaveAs Filename:=Aname & ".xls"
    wkb.Worksheets(1).Name = "Excpt Pricing" 
    wkb.Worksheets(2).Name = "Excpt Profee"

I thought I would be able to reference it easily with:

Code:
wkb.Worksheets(1)

If I make it save the workbook that I create as "PRICING.xls" and name the sheet "FILTER" then it works, but then that defeats the purpose of the dynamic sheet name....I am using the FIND and REPLACE tool to replace the references (tried manually too, not that theres difference in results) with different things. Any ideas?
 

ragnar12

Board Regular
Joined
May 1, 2013
Messages
113
Errors that I am getting when I try to fix it.

ERROR 424, when I only change reference (without adding Dim part).
Error 91, when I add the Dim wkb stuff.

(this is in a different module for testing until I get the bugs out and don't have to run the entire macro everytime)
 

ragnar12

Board Regular
Joined
May 1, 2013
Messages
113
Bump

I am still trying to reference this workbook further in the macro. I have tried simply refering to:

wkb
Aname
wkb.Worksheets(1)
Aname.xls

If I give the workbook a static name or refer to the name of the workbook after its created then the macro works, so I know that it is my referencing that is the problem. There is more info above and I am glad to give any more insight into my problem that you may need. Any help would be greatly appreciated! Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,214
Messages
5,594,876
Members
413,946
Latest member
richelg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top