CODEWITHSUNDEEP
vbaexcel
lukas rajnoha
lukas rajnoha

Reputation: 51

VBA - Copying cells across Workbooks

I'm writing a VBA program that changes the visuals of an excel database. At the end, I need to add a "header" (5 rows of text) at the top. The problem is, I cannot insert the text with VBA, since it contains letters (for ex. á, é...) that aren't compatible with VBA, I need to insert that text from another excel file. The macro I have is stored in a standalone excel workbook, that also contains the cells of the header I need to copy into my database. The problem is, the name of the excel files I am working with varies. Is there a way I could switch between those 2 files and copy cells across them. Can I store the name of the excel file I am working with and later use it in the VBA code to switch between the workbooks?

Upvotes: 1

Views: 102

Answers (1)

Ryan R
Ryan R

Reputation: 31

Not sure if this 100% answers your question but hope it helps, you can open and store both workbooks as objects using: 

Dim wb as Workbook, wb2 as Workbook 
Set wb  = Workbooks.Open("C:\User\Sample_Workbook_File_Path_1.xlsx")
Set wb2  = Workbooks.Open("C:\User\Sample_Workbook_File_Path_2.xlsx")

From there you can call values from either workbook using things like: 

'to get the second workbooks excel file name into a worksheet: "Sample_Workbook_2"
wb.Worksheets("Sample_Worksheet").Range("A1").Value = wb2.Name 

'to copy files 
wb2.Worksheets("Second_Workbooks_Worksheet").Range("A2:A100").Copy _
   wb.Worksheets("Sample_Worksheet").Range("A2") 

'Alternatively you can store the entire workbooks path name instead of the file name using: 
wb.Worksheets("Sample_Worksheet").Range("A1").Value = wb2.Path

Upvotes: 1

Related Questions