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]

I was just meaning if you wanted to not copy all this code to 25 buttons.
You need to keep the code I sent you.
And in all the Buttons you could just use this code in those buttons.
A call command activates a script you already have:
Here is that Script:

Sub Master()
'Modified  10/19/2022  11:01:38 PM  EDT
Call Copy_Range_To_Summary
End Sub

 

[My Aswer Is This]

If you wanted to run the script from your Summary sheet, I could write a code where you enter the sheet name of the sheet you want to copy from.
If you're interested let me know

 

[Newbie2022]

Ya sure, sounds awesome. Thanks

I appreciate you taking the time to help me.

 

[Automatrix]

Make a connection to each sheet in Power Query and append the connections into one summary table. No VBA required in that case.

 

[My Aswer Is This]

Ya sure, sounds awesome. Thanks

I appreciate you taking the time to help me.

Ya sure, sounds awesome. Thanks

I appreciate you taking the time to help me.

So, we have several ways to do this:
1. We have an Inputbox popup, and you enter the sheet name in the inputbox.
2. You enter the sheet name into a cell on the summary sheet and press a button and the script runs the script for that sheet.
The second choice may be easier for you
So, you tell me what cell you want to enter the sheet name in on the summary sheet.
Say something like Range("G3") in case you're not sure what that is That means Column G Row 3

 

[My Aswer Is This]

So using the second choice put this script in a Button on the sheet named "Summary"
Then enter a sheet name in Range("L2")
And then press the button.

Now test this and if it works Ok then leave it the way it is or Modify the Range to what you want it,
Look in the script and you will see Range("L2") just change it to what you want.

Sub Sheet_Names()
'Modified  10/20/2022  4:21:33 AM  EDT
Application.ScreenUpdating = False
On Error GoTo M
Dim ans As String
ans = Sheets("Summary").Range("L2").Value ' Modifyt this Range if you want
Sheets(ans).Activate
Call Copy_Range_To_Summary
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "You entered  " & ans & vbNewLine & "There is no sheet by that name" & vbNewLine & "Try again"
Application.ScreenUpdating = True

End Sub

 

[Newbie2022]

So using the second choice put this script in a Button on the sheet named "Summary"
Then enter a sheet name in Range("L2")
And then press the button.

Now test this and if it works Ok then leave it the way it is or Modify the Range to what you want it,
Look in the script and you will see Range("L2") just change it to what you want.

Sub Sheet_Names()
'Modified  10/20/2022  4:21:33 AM  EDT
Application.ScreenUpdating = False
On Error GoTo M
Dim ans As String
ans = Sheets("Summary").Range("L2").Value ' Modifyt this Range if you want
Sheets(ans).Activate
Call Copy_Range_To_Summary
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "You entered  " & ans & vbNewLine & "There is no sheet by that name" & vbNewLine & "Try again"
Application.ScreenUpdating = True

End Sub

Thanks a bunch, I’ll give that a shot!

 

[Newbie2022]

Make a connection to each sheet in Power Query and append the connections into one summary table. No VBA required in that case.

Thanks for the suggestion. I can give that a try.

 

[My Aswer Is This]

So, we have not heard back from you. What answer did you use?