• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

macro to copy cell and row, paste in new sheet in first empty row

T

tpheath

New Member
Cell H3 contains a date, formated mmm,d,yyyy

B23:M23 contains numeric data.


I need to copy H3 and B23:M23 to a new sheet titled "Historical"

1. H3 should go in column A on Historical

2. B23:M23 should be entered in the same column name, B-M

3. 1 and 2 from above should be in the same row. The row should be the first available empty row.


I would prefer that the data be entered into a table, but this is not a requirement.
 
Tpheath

I have attached a macro below which will help you out

On the Historical Page you will need to ensure you have a Header Row in Row 1 before you run the macro the first time


Copy the following code to a Code Module in VBA

[pre]
Code: 
Sub CopyHistData()

Dim SrcSheet As String
Dim DestSheet As String

SrcSheet = "Sheet1" 'Change to suit
DestSheet = "Historical"  'Change to suit

Sheets(SrcSheet).Range("H3").Select
Selection.Copy

Sheets(DestSheet).Activate
Range("A1").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste

Application.CutCopyMode = False

Sheets(SrcSheet).Activate
Range("B23:M23").Select
Selection.Copy

Sheets(DestSheet).Activate
Range("B1").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste

Application.CutCopyMode = False

Sheets(SrcSheet).Activate

End Sub
[/pre]
 
Tpheath


This macro will do the same as the above but will allow the data on the Historical sheet to be a Data Table

[pre]
Code: 
Sub CopyHistData()

Dim SrcSheet As String
Dim DestSheet As String 

SrcSheet = "Sheet1" 'Change to suit
DestSheet = "Historical" 'Change to suit

Sheets(DestSheet).Activate
Range("B1").Select
Selection.End(xlDown).Select
Selection.ListObject.ListRows.Add AlwaysInsert:=True

Sheets(SrcSheet).Activate
Range("H3").Select
Selection.Copy

Sheets(DestSheet).Activate
Range("A1").Select
Selection.End(xlDown).Select
ActiveSheet.Paste

Application.CutCopyMode = False

Sheets(SrcSheet).Activate
Range("B23:M23").Select
Selection.Copy

Sheets(DestSheet).Activate
Range("B1").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste

Application.CutCopyMode = False

Sheets(SrcSheet).Activate

End Sub
[/pre]
 
You must log in or register to reply here.
Back
Top