VBA Copy Paste from multiple worksheets to one destination sheet

[Newbie2022]

Hello Excel VBA experts!

I am a beginner at VBA and finding it challenging to write a code that works for my needs, after numerous failed attempts thought of seeking your help.

My excel file has 25 worksheets for different medical departments (named like OPDRheumat, OPDRespo, etc) and there is one summary sheet named Summary. So in each 25 worksheets there are calculations which gives results which have to be summarized.

Results are in Col AA20 to AH30 of each of these 25 sheets
I am trying to write a VBA which could help copy data over from these 25 sheets to 1 summary sheet after finding the next empty row.

Note: These 25 sheets are updated by their respective departments at different times and I have to summarize it only on a weekly basis.

I would truly appreciate your help. Thanks

 

[My Aswer Is This]

So you want to copy the Range "AA20:AH30" from 25 sheets and paste the values into a sheet named: "Summary"

Is that correct?
Are there only 26 sheets in the workbook?
If not then what are the names of these 25 sheets
Or will it be from sheet 5 to 30 or what?
And where on The Summary sheet will we be pasting these ranges?

And why did you point this out:
You said:
"Note: These 25 sheets are updated by their respective departments at different times and I have to summarize it only on a weekly basis."
Do I really need to know that to write a script?

 

[Newbie2022]

So you want to copy the Range "AA20:AH30" from 25 sheets and paste the values into a sheet named: "Summary"

Is that correct?
Are there only 26 sheets in the workbook?
If not then what are the names of these 25 sheets
Or will it be from sheet 5 to 30 or what?
And where on The Summary sheet will we be pasting these ranges?

And why did you point this out:
You said:
"Note: These 25 sheets are updated by their respective departments at different times and I have to summarize it only on a weekly basis."
Do I really need to know that to write a script?

Thanks for looking into this. I appreciate it!

A. Yes, correct. So results from AA20: AH30 from the 25 sheets have to be copied over to Summary sheet which is in the 26th Sheet

B. More sheets get added time-to-time & it depends on the analysis that needs to be done so is named accordingly. They won’t feed into the summary sheet.

C. On the summary sheet, the results are entered in Col A through H. I was trying to get each week’s summary results added from the next available blank row without deleting previous week’s summary.

D. I was thinking if each sheet has a button which the admin could click on once they have added their results so it’s copied over to the summary sheet it would be helpful. And since they are done at different times throughout the week by different admins, I believe (could be wrong due to my limited knowledge of VBA) we can’t have one code which can go into loops and copy the data over hence shared it.

Thanks

 

[My Aswer Is This]

Put this script on one of the sheets where you want the Range copied from.
And I assume you have a sheet named "Summary"

If this works let me know. And I will tell you how to activate the script from all the other sheets.

Sub Copy_Range_To_Summary()
'Modified  10/19/2022  2:20:21 AM  EDT
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row + 1
Range("AA20:AH30").Copy Sheets("Summary").Cells(Lastrow, 1)
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

Instead of putting a Button in all 25 sheets with the same script.
We can use a call command. So in all the other Buttons on the other sheets we just put a script like this:

Call Copy_Range_To_Summary
This way if you ever want to modify the script we only modify the Master script

 

[Newbie2022]

Put this script on one of the sheets where you want the Range copied from.
And I assume you have a sheet named "Summary"

If this works let me know. And I will tell you how to activate the script from all the other sheets.

Sub Copy_Range_To_Summary()
'Modified  10/19/2022  2:20:21 AM  EDT
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row + 1
Range("AA20:AH30").Copy Sheets("Summary").Cells(Lastrow, 1)
Application.ScreenUpdating = True
End Sub

Thanks for your prompt reply.

I tried the code and it worked but there is a #REF! Error on the summary sheet. The values in the results table on the OPDRheumat sheet (where we are copying from) has cell references. Is there a way for the code to copy over the formatting and then values to prevent this error?

Thanks

 

[My Aswer Is This]

Try this:
I added one line of code showing what sheet this data came from
If you do not like this remove that line of code:
And I modified the code to fix your problem with it

Sub Copy_Range_To_Summary()
'Modified  10/19/2022  3:12:01 PM  EDT
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row + 1
Range("AA20:AH30").Copy
Sheets("Summary").Cells(Lastrow, 1).PasteSpecial xlPasteValuesAndNumberFormats
Lastrow = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Summary").Cells(Lastrow, 1).Value = "Above data comes from sheet named  " & ActiveSheet.Name & "  " & Now()
Application.CutCopyMode = False
    
Application.ScreenUpdating = True
End Sub

 

[Newbie2022]

Try this:
I added one line of code showing what sheet this data came from
If you do not like this remove that line of code:
And I modified the code to fix your problem with it

Sub Copy_Range_To_Summary()
'Modified  10/19/2022  3:12:01 PM  EDT
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row + 1
Range("AA20:AH30").Copy
Sheets("Summary").Cells(Lastrow, 1).PasteSpecial xlPasteValuesAndNumberFormats
Lastrow = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Summary").Cells(Lastrow, 1).Value = "Above data comes from sheet named  " & ActiveSheet.Name & "  " & Now()
Application.CutCopyMode = False
   
Application.ScreenUpdating = True
End Sub

This worked like magic!!!! I can’t thank you enough for coming to my rescue.

I was struggling for a week to get this result. Can you please recommend any material/ test VBA codes I could refer to, to get better at VBA.

Thanks

 

[My Aswer Is This]

One more suggestion:
Are you using this script as what they call a Module script so it is Stored in Vba Project window.

Or are you putting this script in a Sheet Activex button?

 

[Newbie2022]

One more suggestion:
Are you using this script as what they call a Module script so it is Stored in Vba Project window.

Or are you putting this script in a Sheet Activex button?

One more suggestion:
Are you using this script as what they call a Module script so it is Stored in Vba Project window.

Or are you putting this script in a Sheet Activex button?

I inserted it as new module and it worked. I remember you suggested using a call command so if we are going to use that I can move it to only 1st sheet. If you can share the steps I can make those changes. Please and thank you.