Using VBA for copying from multiple sheets combine into one sheet

[KDP36]

Hi all,

VBA beginner here. Wondering how come my codes don't work and I can't find a way to debug it. I thought the code was pretty straight forward but just don't understand how it's not right.
The goal here is simply creating a worksheet called 'Summary', then copy data from A1:C5 from Max, Min, and Projection three worksheets respectively and paste into 'Summary' at 'A1'.

Sub Pasteinto()
Worksheets.Add
Sheets(4).Name = "Summary"
Worksheets("Max").Activate
Worksheets("Max").Range("A1:C5").Select
Selection.Copy
Worksheets("Summary").Activate
Range("A1").Select
ActiveSheet.Paste
Worksheets("Min").Activate
Worksheets("Min").Range("A1:C5").Select
Selection.Copy
Worksheets("Summary").Activate
Range("A6").Select
ActiveSheet.Paste
Worksheets("Projection").Activate
Worksheets("Projection").Range("A1:C5").Select
Selection.Copy
Worksheets("Summary").Activate
Range("A11").Select
ActiveSheet.Paste
End Sub

Of course, if you have an easier way to do this, please tell me so. As you can see, my way is very tedious as I don't know how to incorporate a loop in this procedure.
Thanks for all the help!

Stan

 

[JLGWhiz]

Hi all,

VBA beginner here. Wondering how come my codes don't work and I can't find a way to debug it. I thought the code was pretty straight forward but just don't understand how it's not right.
The goal here is simply creating a worksheet called 'Summary', then copy data from A1:C5 from Max, Min, and Projection three worksheets respectively and paste into 'Summary' at 'A1'.

Sub Pasteinto()
Worksheets.Add
Sheets(4).Name = "Summary"
Worksheets("Max").Activate
Worksheets("Max").Range("A1:C5").Select
Selection.Copy
Worksheets("Summary").Activate
Range("A1").Select
ActiveSheet.Paste
Worksheets("Min").Activate
Worksheets("Min").Range("A1:C5").Select
Selection.Copy
Worksheets("Summary").Activate
Range("A6").Select
ActiveSheet.Paste
Worksheets("Projection").Activate
Worksheets("Projection").Range("A1:C5").Select
Selection.Copy
Worksheets("Summary").Activate
Range("A11").Select
ActiveSheet.Paste
End Sub

Of course, if you have an easier way to do this, please tell me so. As you can see, my way is very tedious as I don't know how to incorporate a loop in this procedure.
Thanks for all the help!

Stan

Note the changes in red font. If you don't specify where to add the sheet, Excel goes to default, which most likely will not be sheet 4. Then, once the sheet is added, it becomes the active sheet, so you can besure the right sheet gets the name by using "ActiveSheet.Name =". Everything else is the same.

Sub Pasteinto()
Worksheets.Add [COLOR=#b22222]After:=Sheets(Sheets.Count)
[/COLOR][COLOR=#b22222]ActiveSheet.[/COLOR]Name = "Summary"
Worksheets("Max").Activate
Worksheets("Max").Range("A1:C5").Select
Selection.Copy
Worksheets("Summary").Activate
Range("A1").Select
ActiveSheet.Paste
Worksheets("Min").Activate
Worksheets("Min").Range("A1:C5").Select
Selection.Copy
Worksheets("Summary").Activate
Range("A6").Select
ActiveSheet.Paste
Worksheets("Projection").Activate
Worksheets("Projection").Range("A1:C5").Select
Selection.Copy
Worksheets("Summary").Activate
Range("A11").Select
ActiveSheet.Paste
End Sub

 

[Teeroy]

To incorporate looping into your code and make it smaller and more manageable try the following.

Sub Pasteinto()
Dim wkshtNew As Worksheet, rDest As Range, shtName
Set wkshtNew = Worksheets.Add(After:=Sheets(Sheets.Count))
wkshtNew.Name = "Summary"
Set rDest = wkshtNew.Range("A1")
For Each shtName In Array("Max", "Min", "Projection")
    Worksheets(shtName).Range("A1:C5").Copy Destination:=rDest
    Set rDest = wkshtNew.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Next
wkshtNew.Activate
End Sub

 

[jsotola]

I can't find a way to debug it.

resize your vba editor so that it covers 1/2 screen (left or right)

resize workbook so that it covers the other 1/2 of screen

click in your code (anywhere between the first line and the last line)

press F8 key on keyboard (single-step)

you will see a yellow highlight bar ( it indicates the line that will execute when you press F8 again )

watch the workbook as you single-step through the code and see if it does what you expect

 

[KDP36]

Thank you JLGWhiz! That was very informative, pointing out where exactly my misunderstanding was.
It was very helpful, thank you!

 

[KDP36]

Thanks Teeroy! I still have 2 questions:
1. For object shtName, how come you didn't clarify what kind of object is it, as you did with wkshtNew and rDest?
2. Could you explain to do what does the last command within the for loop means? I don't see a paste command, and is it simply setting rDest to a range of values starting from column A and whatever rows that end up? I don't fully understand how did it exactly 'pasted' from what was copied earlier.
Thanks for your patience in asking these questions.

 

[Teeroy]

Hi KDP36,

Most people in this forum will happily answer a sensible question asked politely. To answer your questions:

1. Dimensioning shtName without a data type makes it a variant (I could have added "as variant" as well but I used the shorter method). A variant data type is necessary to use as an element in a For Each type Loop.
2. The destination for the copy is set within the .Copy command (see below). The copy is pasted to the Range identified as rDest. The last line in the loop sets the new position of rDest as the last cell in column "A" + 1 row.

Worksheets(shtName).Range("A1:C5").Copy [B][COLOR=#ff0000]Destination:=rDest[/COLOR][/B]

Hope this helps and enjoy learning VBA, it allows for great extension to Office products (and keeps you from going nuts from repetitive tasks ).

 

[JLGWhiz]

Thank you JLGWhiz! That was very informative, pointing out where exactly my misunderstanding was.
It was very helpful, thank you!

You're welcome,
Regards, JLG