VBA: Dynamic Workbook Name Reference

[ragnar12]

I am using:

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)

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

 

[Momentman]

You need to add a new variable for the workbook being added like this

    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]

For the ranges that are non-contiguous, you could use

Union(Range("E1:E" & lastrow), Range("G1:G" & lastrow)).Copy

to copy just column E and G

 

[ragnar12]

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]

You are welcome

 

[ragnar12]

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:

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):

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:

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]

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]

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!