Copy column in one workbook and paste into another. Creating new column

Question

I need to collect data every month from different workbooks and then transfer it into one master data workbook.

MyFile = Dir
Range("C2:D18").Copy
ActiveWorkbook.Close False

erow = Sheet1.Cells(3, 2).End(xlUp).Offset(0, 1).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, erow), Cells(erow, erow))

The code above transfer my data and put them correctly for that month, column C and D. When I'm trying to transfer data the next month, it replace the ones in c and D. Instead I want the new data to get into column E and F, and the month after that G and H.

How could this be done?

Use indexes and loops so that your index is increased by 2 (2 columns) and the loop also read your data from multiple workbooks — Kanike Vamshi Krishna, Dec 15 '15 at 12:42

score 0 · Answer 1 · edited May 23 '17 at 11:52

I suggest that you take a look at this answer to see how to use Dir() properly for reading multiples files. I'm not sure that you need to read more than one file at a time regarding your explanation but it is always good to see a clean example! ;)

Btw, note that .Offset(0, 1) is useless in the erow = ... as you use .Row at the end, and your Offset only affect the column of the cell.

This should work fine if you initial code do what you want :

MyFile = Dir
Range("C2:D18").Copy
ActiveWorkbook.Close False

eCol = Sheet1.Cells(3, 2).End(xlUp).End(xlToRight).Offset(0, 1).Column
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Cells(2, eCol)

Copy column in one workbook and paste into another. Creating new column

1 Answers1