Specific rows of one sheet to last rows of multiple sheets

Shoesbologna

New Member
Joined
Sep 3, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hi all,
Have been looking hard here and haven't quite found what I'm looking for.
Essentially, I want to copy a data set onto a sheet (titled "work) and have a macro take the different rows of info and copy and past to the next available rows on different sheets.
I've recorded a macro and have gotten this:

The columns are all correct, but the specific row 61's just happen to be the next available row, right now. Tomorrow it'll be row 62.

Thanks in advance!

VBA Code:
Sub CopyAll()
'
' CopyAll Macro
'

'
    Sheets("Work").Select
    Range("C15:Q15").Select
    Selection.Copy
    Sheets("Sheet4").Select
    Range("B61").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Work").Select
    Range("C6:Q6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet5").Select
    Range("B61").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Work").Select
    Range("C7:Q7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet5").Select
    Range("U61").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Work").Select
    Range("C8:Q8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet6").Select
    Range("B61").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Work").Select
    Range("C9:Q9").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet7").Select
    Range("B61").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Work").Select
    Range("C10:Q10").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet7").Select
    Range("U61").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Work").Select
    Range("C11:Q11").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet8").Select
    Range("B61").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Work").Select
    Range("C12:Q12").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet8").Select
    Range("U61").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Work").Select
    Range("C13:Q13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet9").Select
    Range("B61").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Work").Select
    Range("C14:Q14").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet9").Select
    Range("U61").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub
 
Last edited by a moderator:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Hi,
the problem with the macro recorder is that it assumes you want to go to the same cell every time you run the macro.
to go to the last used row of a sheet add .SpecialCells(xlCellTypeLastCell).row
depending on your requirements you may need to use this withing cells()
 
Upvote 0
Hi & welcome to MrExcel.
You can do it like this
VBA Code:
Sub CopyAll()
   With Sheets("Work")
      .Range("C15:Q15").Copy
      Sheets("Sheet4").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
      .Range("C6:Q6").Copy
      Sheets("Sheet5").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
      .Range("C7:Q7").Copy
      Sheets("Sheet5").Range("U" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
      .Range("C8:Q8").Copy
      Sheets("Sheet6").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
   End With
End Sub
 
Upvote 0
Hi & welcome to MrExcel.
You can do it like this
VBA Code:
Sub CopyAll()
   With Sheets("Work")
      .Range("C15:Q15").Copy
      Sheets("Sheet4").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
      .Range("C6:Q6").Copy
      Sheets("Sheet5").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
      .Range("C7:Q7").Copy
      Sheets("Sheet5").Range("U" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
      .Range("C8:Q8").Copy
      Sheets("Sheet6").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
   End With
End Sub
Thank you so much! This is great!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top