Copying from multiple locations to the bottom of a column (always changing)

[figgylynn1023]

I have built a macro to pull data from multiple locations and copy it into a single excel template. I have reserved the two far left columns of the template for formula calculations, and want the macro code to ensure that the copying and pasting from the other excel docs to the template always starts the paste into the first open cell at the bottom of column C (wherever that happens to be when that document gets copied).

I've tried to following code, but it came up as an error for me:

Range("C1")Select
Selection.End(xlDown).Offset(1,0).Select
ActiveSheet.Paste

Any thoughts?

 

[Dan Waterloo]

Hi figgylynn1023,

Welcome to the Board.

What is the error message you received?

I believe the first line in your code should be

Range("C1").Select

rather than

Range("C1")Select

but there's likely more to it than that.

 

[Smitty]

Welcome to the Board!

You didn't post how you're copying, but you can copy and paste to the first empty row like this:

Sheets("Sheet1").Range("A1").Copy Sheets("Sheet2").Cells(Rows.Count,"C").End(xlUp).Offset(1)

HTH,

 

[figgylynn1023]

Thank you both for your suggestions. I ended up having another issue combine with this...a FillDown issue that made the copy area screwy.

I ended up using a formula of:

Windows("bf" & Day & "_2856.txt").Activate
ActiveSheet.UsedRange.Copy
Windows("bf" & Day & "_113.txt").Activate
Range("A65536").Select
Selection.End(xlUp).Offset(1,0).Activate
ActiveSheet.Paste

After I figured out my FillDown issue with:

Windows("bf" & Day & "_5298.txt").Activate
Range("A1").EntireColumn.Insert
Range("A1:A" & Range("B" & Rows.Count).End(xlUp).Row).Value="5298"

Thank you again for your assistance!