VBA combining worksheets - small truncating issue

[SamFunzie]

Hello all,

Probably a simple fix that I just cannot seem to figure out... I have been using the following macro quite successfully to "merge" portions (as defined by the range) of multiples worksheets (in the same book) into one. However, an issue has arisen that I cannot seem to fix.

The code does exactly what I want EXCEPT now it's cutting off (or pasting) the last two lines of the previous pasting operation and I am missing information... I thought of adding a couple of "blank rows" as a pad between "tables"... but I cannot get that to work either. Suggestions???

With many thanks....

S

Sub SummarizeSheets()
Dim ws As Worksheet

Application.ScreenUpdating = False
Sheets("Summary").Activate

For Each ws In Worksheets
   If ws.Name <> "Summary" Then
      ws.Range("B1:G17").Copy
      ActiveSheet.Paste Range("A65536").End(xlUp).Offset(1, 0)
   End If
Next ws
End Sub

 

[Fluff]

Will you always have values in col A?
If not is there another col that will always have a value on every row?

 

[SamFunzie]

Will you always have values in col A?
If not is there another col that will always have a value on every row?

Hi there...

I am not sure that I understand what you are asking but allow me to try to answer...

Each worksheet has two tables representing two locations (B1:G40 and I1:N32) with values (times)... I do time studies every so often (say quarterly) to get a snapshot on what the drivers are doing.
Right now I just do it this way, I am looking at possible dashboards etc going forward and let them do all the work...

If there was a way to select the entire table (either one by choice) as a whole and paste them successfully onto a master sheet but... my excel VBA hasn't advanced that far yet ...

I hope that answers your question...

 

[Fluff]

Can you have blank cells in either B1:B40 or I1:I32, but have data on those rows in the other columns?

 

[SamFunzie]

Can you have blank cells in either B1:B40 or I1:I32, but have data on those rows in the other columns?

I did not design the tables, but yes there are a few "spacer" cells within those ranges. Is there a way to use Selection.CurrentRegion.Select to choose the two tables as a whole? (apologies if the question is out of line...)

S

 

[Fluff]

Is there any column that will always have data?

 

[SamFunzie]

Is there any column that will always have data?

For the most part all columns (within those ranges) in either table will always have data

 

[Fluff]

But will any column ALWAYS have data?
If your code is overwriting your data it's probably because of blank cells in colA. Which is why I am asking.

 

[SamFunzie]

Both tables have six columns; there will be data in all six.

 

[Fluff]

In that case there should not be a problem with your code.