Help with macro - copy and paste row to multiple rows based on data availability

[Josiev]

Hello Excel Experts,

I need help in updating my macro. The workbook's 2nd worksheet has a formula on Row 2 which needs to be copied down depending on the data availability on the 1st worksheet. The 2nd worksheet is the source of the pivot table on the 3rd worksheet which is then refreshed after all the formulas on 2nd worksheet are copied. This 1st worksheet may have 1 row of data or hundreds of rows of data, it would vary...Currently the macro I built to copy rows stops at a certain row.

My question is....is there a way to update this macro to make sure that the rows that get copied on the 2nd worksheet are exactly the same number of rows from the 1st worksheet?

Below is my macro:

Sub sbCopyRangeToAnotherSheet()

'Method 1
Sheets("Sheet1").Range("A1:B10").Copy Destination:=Sheets("Sheet2").Range("E1")

'Method 2
'Copy the data
Sheets("Report Data").Range("A1:B10").Copy
'Activate the destination worksheet
Sheets("Sheet2").Activate
'Select the target range
Range("E1").Select
'Paste in the target destination
ActiveSheet.Paste

Application.CutCopyMode = False

End Sub

--------------------------------------------------------------------------------------------------- Attached is the workbook for your reference. Any assistance you can provide is greatly appreciated.

 

[Luke M]

Hi Josiev,

I'd actually recommend using a table where your original data is and having the formulas auto-populate there. Then you can base the PivotTable off of that. This eliminates the copying issue, saves memory, and also lets the PivotTable only have to look at the rows w/ data, as opposed to every row. You can see this solution in GL Simple. The macro/button there is just one line, refreshing the pivot table.

If perhaps the Raw Data is a data dump from somewhere and you have to use this layout, I went ahead and answered your original question as well. This is seen in QL Copy. Look them over, let me know which one you like best.

NOTE: To attach both files, I had to trim out some of the raw data, so you will want to copy that back in for your actual file.

 

[Josiev]

Hi Josiev,

I'd actually recommend using a table where your original data is and having the formulas auto-populate there. Then you can base the PivotTable off of that. This eliminates the copying issue, saves memory, and also lets the PivotTable only have to look at the rows w/ data, as opposed to every row. You can see this solution in GL Simple. The macro/button there is just one line, refreshing the pivot table.

If perhaps the Raw Data is a data dump from somewhere and you have to use this layout, I went ahead and answered your original question as well. This is seen in QL Copy. Look them over, let me know which one you like best.

NOTE: To attach both files, I had to trim out some of the raw data, so you will want to copy that back in for your actual file.

Hi Luke....I agree...using a table would be better. However, the source of the data has missing components that could group all the data in a desired summarized form. On the 2nd worksheet, there's a lookup formula that categorize a specific expense in a designated bucket (ie, Salaries, travel, etc). Also, there is a formula to designate the posting date to a specific month in a number format so that the pivot table summarizes all the accounts by month from oldest to newest. The 1st file you updated is the best one that suits our needs. This is tremendously helpful...thank you so much for the quick response!