I have several workbooks with approximately 30 worksheets each. I need to copy certain cells (B4, L30, H34, and K63) to paste in another spreadsheet.
Col A Col B Col C
0826[B4] 0827[B4]
X [H34] [H34]
Y [L30] [L30]
Z [K63] [K63]
I created a macro by recording, but I need to be able to either select the worksheet (ie 0826) or significantly more difficult for me have the macro to move on to the next sheet. I will also have to move to the right looking for the next empty cell on the "Combine" sheet. Below is the macro I am starting with. Any guidance would be most appreciated on how to write the macro to move to the next sheet in the original workbook and continue copying and pasting to the right in the combine sheet.
Sub Macro5()
'
' Macro5 Macro
'
'
Sheets("0826").Select
Range("B4").Select
Selection.Copy
Sheets("Combine").Select
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("0826").Select
Range("H34").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Combine").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("0826").Select
Range("L30").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Combine").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("0826").Select
Range("K63").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Combine").Select
Range("B6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("0827").Select
Range("B4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Combine").Select
Range("C1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("0827").Select
Range("H34").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Combine").Select
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("0827").Select
Range("L30").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Combine").Select
Range("C3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("0827").Select
Range("K63").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Combine").Select
Range("C6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
Bookmarks