copy large sheet to master sheet

[KeythStone]

Hello all,

I am trying to write vba script to automate copy of worksheets to a master sheet and I've seen all sorts of examples out there as well as a few internally at work. But my issue exceeds the limits placed on them and I was hoping it wasn't a universal constraint that I'm running into.

Issue: The sheets to copy are quite large averaging 11000 rows. the data to copy is dynamic meaning the volume changes, so finding the lastusedrow or something like that is needed. Most codes i've found, though, are limited to range("A65536"). I'm using 2007 which allows for more row count.

Goal: To get multiple sheets to a master sheet, ultimately to be imported into access.

Any help would be great.

Stay Smooth!!

 

[Richard Schollar]

Will the last row of data always contain a value in Column A? If so, just determine the last row of col A using:

Dim   LastRowNumber As Long
With Sheets(x)  'where x is the string name or index of sheet of interest
  LastRowNumber = .Cells(.Rows.Count,"A").End(xlUp).Row
  .Range("A2:G" &   LastRowNumber).Copy
  'more code...
End With

 

[KeythStone]

Thanks,

I'm trying that right now. My biggest issue with this process is pasting the selection succinctly, meaning sheet1 (11000 rows) paste in master, then sheet2 (11000 rows) paste just below the @ (row 11001).

Thanks,
Smooth

 

[Richard Schollar]

You can use the same process to find the last row on the master sheet and then start the paste immediately below:

Range("A1:G1000").Copy

With Sheets("Master")
  'identify last row with data and paste into next cell down:
  .Cells(.Rows.Count,"A").End(xlUp).Offset(1,0).PasteSpecial
End With