2 part macro question (sequence & order)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a workbook that I am using the first worksheet for data collection.
Everytime the workbook is opened I would like a sequence number populated
automatically. (i.e. 1,2,3,etc.)
I created a macro that is assigned to a "submit" button. When the button is
selected the data transfers to the second worksheet. How do I make the data
insert in the next empty row on the second worksheet? I appreciate anyone's
help on this.

Thanks
 
One way:


Dim rDest as Range
With Sheets("Sheet2")
Set rDest = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With

copy to rDest

This assumes that column A will always be populated in a populated row -
change to suit.
 
If this is what I have where do I insert your suggestion?

Range("B2").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("B3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("B2").Select
ActiveSheet.Paste
Range("B17:B18").Select
Sheets("Sheet1").Select
Range("B4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("C2").Select
ActiveSheet.Paste
Range("C13:C14").Select
Sheets("Sheet1").Select

End Sub
 
Thanks JE. If this is what I have where do I insert your suggestion?

Range("B2").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("B3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("B2").Select
ActiveSheet.Paste
Range("B17:B18").Select
Sheets("Sheet1").Select
Range("B4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("C2").Select
ActiveSheet.Paste
Range("C13:C14").Select
Sheets("Sheet1").Select

End Sub
 
Well, one way, based on your existing pattern:

Dim rDest As Range
With Sheets("Sheet2")
Set rDest = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With
rDest.Resize(1, 3).Value = Application.Transpose( _
Sheets("Sheet1").Range("B2").Resize(3, 1).Value)
 
Back
Top