Need help with a macro

[Johnny_Human_Torch]

Hi all, sorry if this is the wrong place for this or if it's so basic it's been covered. If it has, I'd appreciate a nudge in the right direction.

I recorded a simple macro that copies values from sheet one coming from various unformatted places and moves them to sheet two in the same row. It works great, however, because I recorded it, it does the same thing over and over into the same row.

My question is, how do I make the macro paste to a row, then advance the cursor to the next row, then paste it again across the next sequential row?

I know my range is defined as the particular cell, and I should have a more general definition, but again, I don't know how. And I'm not sure how to advance the cursor to the next row.

I'll post what I have in the next post for anyone that would like to see it.

 

[Johnny_Human_Torch]

' Keyboard Shortcut: Ctrl+Shift+M
'
Range("B1").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("B14").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("H2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("B15").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("I2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("B16").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("J2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("B17").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("K2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("B18").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("L2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("B19").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("M2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Sheet1").Select
Range("D14").Select
Selection.Copy
Sheets("Sheet2").Select
Range("N2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Sheet1").Select
Range("D15").Select
Selection.Copy
Sheets("Sheet2").Select
Range("O2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Sheet1").Select
Range("D16").Select
Selection.Copy
Sheets("Sheet2").Select
Range("P2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("D17").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("Q2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Sheet1").Select
Range("D18").Select
Selection.Copy
Sheets("Sheet2").Select
Range("R2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Sheet1").Select
Range("D19").Select
Selection.Copy
Sheets("Sheet2").Select
Range("S2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

 

[Norie]

Can we see the code?

And can you explain further what you are copying/pasting and where you are copying/pasting from/to?

 

[Johnny_Human_Torch]

Can we see the code?

And can you explain further what you are copying/pasting and where you are copying/pasting from/to?

I receive a sheet from another department with data all over the place. I only need a name, which goes in column 2 and the pertinent data associated with the name which goes in row H through S. My department previously did all of that data entry on their own. I don't want to do that

 

[Norie]

Does this untested code work, or at least give you some ideas?

Dim NextRow As Long
Dim rng As Range
    
    NextRow = Sheets("Sheet2").Range("G65536").End(xlUp).Row + 1

    Set rng = Sheets("Sheet1").Range("B1")
    
    rng.Copy Sheets("Sheet2").Range("G" & NextRow)
    
    Set rng = Sheets("Sheet1").Range("B14:B19")
    
    rng.Copy
    
    Sheets("Sheet2").Range("H" & NextRow).PasteSpecial Transpose:=True
        
    Set rng = Sheets("Sheet1").Range("D14:B19")
    
    rng.Copy
    
    Sheets("Sheet2").Range("N" & NextRow).PasteSpecial Transpose:=True

 

[Joe Was]

You could also loop based on a list of cells or some condition, like:
which searches a list on Sheet1 column: H for values less than 0.2 if found it then copies that rows data to a list on another sheet: Sheet2.

Sub myNewTable()
'Sheet module code, like: Sheet1
Dim myRng As Range
Dim myEnd&, n&, myR&

myEnd = Range("H65536").End(xlUp).Row

Set myRng = Range("H1:H" & myEnd)

For Each Cell In myRng
myR = Cell.Row

If Cell.Value < 0.2 Then
Range("A" & myR & ":H" & myR).Copy _
Destination:=Sheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0)
n = n + 1
End If

Next Cell

MsgBox "Found: " & n & ", Rows!"
End Sub


And to just copy every row: just get rid of the "If" statement, which is the "Test" for value part.