CODEWITHSUNDEEP
vbaexcel
Panos P.
Panos P.

Reputation: 21

How to automatically copy columns from one excel file to another excel file in different sheets

I am really new to visual basic (not even a day old :)) and your help will be greatly appreciated.

I have two excel files, the Source and the Target file. The source file is full of information all in the first tab/sheet in different columns. The Target file is half empty (the rest is pre-populated with default values) and needs to be filled with the same values as the Source file (destination format would be good if we can keep) but in different columns across different tabs.

Could someone please enlighten me with a macro function that can:

(For example:)

1) Take the contents from the Source file/Sheet 1/Column A3 and below and copy it in the Target File/Sheet 1/Column X7 and below

2) Take the contents from the Source file/Sheet 1/Column B3 and below and copy it in the Target File/Sheet 2/Column X5 and below

3) Take the contents from the Source file/Sheet 1/Column C3 and below and copy it in the Target File/Sheet 1/Column Y2 and below and at the same time in the Target File/Sheet 2/Column Z4 and below.

So basically column A goes to the first tab column X of another file, B goes to the second tab column X and C goes to both the first and second tabs, columns Y and Z respectively. (With the possibility the starting row in the Target file, to be different each time.)

If I have the basic structure code, I think I will be able to adapt it with the file locations, workbook/sheet names and columns for my specific problem.

Thank you a lot for your time to answer my question and looking forward to any ideas you might have! Please let me know of course if you need further clarification on something.

Cheers!
Panos

P.S.1. Please note that we want to copy from one file to another (not across different sheets in the same file!)
P.S.2. Is just about copying columns from one excel file to another, I don't mind to have the opening/closing/saving workbook automations or any other automations.

Maybe a starting function could be?

Sub Demo() 

Dim wbSource As Workbook 
Dim wbTarget As Workbook 

‘ First open both workbooks : 
Set wbSource = Workbooks.Open(“”) ‘ <<< path to source workbook 
Set wbTarget = Workbooks.Open("") ' Workbooks.Open(" ") ' <<< path to destination workbook 

'Now, transfer values from wbSource to wbTarget: 

wbTarget.Sheets("Sheet Name?").Range("A3:A9999").Value = wbSource.Sheets("Sheet Name?").Range("X7:X9999") wbSource.Close 

End Sub

Upvotes: 2

Views: 12083

Answers (1)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96791

Here is a very short example:

Sub panos()
  Dim r1 As Range, r2 As Range, N As Long

  Workbooks.Open "C:\TestFolder\source.xlsx"
  N = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
  Set r1 = Sheets("Sheet1").Range("A3:A" & N)

  Workbooks.Open "C:\TestFolder\dest.xlsx"
  Set r2 = Sheets("Sheet1").Range("X7")

  r1.Copy r2
End Sub

Upvotes: 1

Related Questions