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:

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156
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()
 

Shoesbologna

New Member
Joined
Sep 3, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Got it.
can you give a specific example for my cause?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,076
Office Version
  1. 365
Platform
  1. Windows
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
 

Shoesbologna

New Member
Joined
Sep 3, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,076
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,931
Messages
5,545,089
Members
410,652
Latest member
Zot
Top