VBA combining worksheets - small truncating issue

SamFunzie

New Member
Joined
Oct 4, 2021
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
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

VBA Code:
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Will you always have values in col A?
If not is there another col that will always have a value on every row?
 
Upvote 0
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...
 
Upvote 0
Can you have blank cells in either B1:B40 or I1:I32, but have data on those rows in the other columns?
 
Upvote 0
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
 
Upvote 0
Is there any column that will always have data?
 
Upvote 0
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.
 
Upvote 0
In that case there should not be a problem with your code.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top