Use Cell Value in place of name in any part of VBA Code

[sanantonio]

Hi All,

Might not be the last bit of help I need on my current project.

Essentially I'm building a code that opens a new workbook, save it as "CELL VALUE (U2)" (Which is a concat of site and =today() ). Issue is after it opens and saves I need to copy and paste values into it. Then save and close.

Pretty sure I can do the code for saving and closing, but need a little help with the copy and paste part.

Code I have is

    Set wb = ThisWorkbook
    wb.Sheets("Export").Range("A1:I283").Copy
    Workbooks.Open ("?????")
    Workbooks("?????").Worksheets("Sheet1").Range("A1").PasteSpecial xlPasteValues

I've stress tested this copy and paste code, replacing the ?????s with the actual filename (Manually typed in) and it works. But as the filename is fluid and will change dependent on site and date I really need the two ???? sections to be a cell value U2.

I'd love to know how to substitute a part of VBA code for a cell value as this would not only help me with this coding predicament but also others in the future.

Thanks for your help! I've been struggling with this for last couple of hours, google isn't saving me today!

 

[NdNoviceHlp]

It sort of depends on where the file U want opened is located? If your "U2" info is in sheet 1 and just contains the wb name and the file you're trying to open is in the same file location as your current wb, this should work. Also adjust the file extension to suit. HTH. Dave

Workbooks.Open FileName:=ThisWorkbook.Path & "\" & _
                    CStr(Sheets("Sheet1").Range("U2")) & ".xlsm"

 

[sanantonio]

Thanks for coming back to me, but I'm still having issues. Let me give you guys the complete picture so maybe it'll make more sense.

The Macro enabled spreadsheet, FV_AD_Macro 0.1.xlsm is currently saved to my desktop. The "Setup" page currently looks like this:

So U2 has the file name, which is a concat of site number, a business specific denominator, then today() .xlsx.

For the purposes of this exercize I tried inserting the full filepath and name (U4), but ideally this wouldn't be there.

The code currently in the macro is this:

Sub Macro1()
'
' Macro1 Macro
'

Dim wB As Workbook
Dim nPath As String
nPath = "C:\Users\xxxx\Documents\" & ThisWorkbook.Sheets("Setup").Range("U2").Value
Set wB = Workbooks.Add
With wB
    .SaveAs filename:=nPath
End With

    Set wB = ThisWorkbook
    wB.Sheets("Export").Range("A1:I283").Copy
Workbooks.Open filename:=ThisWorkbook.Path & "\" & _
                    CStr(Sheets("Setup").Range("U4"))
                    Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues


'
End Sub

When I run the macro it saves and creates the new spreadsheet, but then dies here:

Essentially what the macro will eventually do is Filter a data table by Site. Then copy and paste that site's information into a new workbook with the naming parameters mentioned above, then close that workbook, then repeat the process for all ten of our sites. The filtering table etc. I can get the code from the "Record Macro" function and the "copy" part of this current code works just fine, but getting it to then paste into the document with the name in U2 on my Setup tab is proving nigh on impossible.

Any help would be appreciated thanks!

 

[NdNoviceHlp]

This should work if I understand U. Dave

Dim wB As Workbook
'Dim nPath As String
'nPath = "C:\Users\xxxx\Documents\" & ThisWorkbook.Sheets("Setup").Range("U2").Value
Set wB = Workbooks.Open(ThisWorkbook.Path & "\" & CStr(Sheets("Setup").Range("U2")))
ThisWorkbook.Sheets("Export").Range("A1:I283").Copy
Workbooks(wB.Name).Sheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues

 

[sanantonio]

OMG that's perfect thanks!

Last one...

When it pastes, it does paste values but it's not respecting the filter from the copy section. So I have a filter on the Data in the table that occupies A1:I283 in "Export" tab, and only want it to copy and paste values the displayed data, but it takes everything? Unfiltered and filtered?

How do I change the copy or paste code to just take what's visible? I've run other VBA and could swear simply pasting values does the trick; but not in this case!

 

[NdNoviceHlp]

Google seems to indicate this should work....

Dim wB As Workbook
Set wB = Workbooks.Open(ThisWorkbook.Path & "\" & CStr(Sheets("Setup").Range("U2")))
With ThisWorkbook.Sheets("Export").Range("A1:I283")
.SpecialCells(xlCellTypeVisible).Copy
End With
'ThisWorkbook.Sheets("Export").Range("A1:I283").Copy
Workbooks(wB.Name).Sheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Dave

 

[sanantonio]

You're a true gent. Sorry I lied there is one more, just to add closing that workbook after the pasting? Not the original workbook the one with the name in U2 ?

 

[NdNoviceHlp]

I assume that U would want to save it...

Workbooks(wB.Name).Close SaveChanges:=True

Dave

 

[sanantonio]

I owe you more than a few beers now! If you're ever in San Antonio let me know!

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Dave