VBA Copy insert into another workbook _ excel 2013

Hi

Couple of lines that may help you below, one finds & selects used range minus row 1 (I assumed by your question that you had headers ?) & the other selects row after last used row of active worksheet.

'Select used range minus headers/row 1
With ActiveSheet.UsedRange
 .Offset(1, 0).Resize(.Rows.Count - 1).Select 'Selects used range in active worksheet minus row 1 using "offset"
 Selection.Copy ' Copies selected range
End With

'Select next empty row
Dim LastRow As String
LastRow = ActiveSheet.UsedRange.Rows.Count 'Finds last row of active worksheet
Rows(LastRow + 1).Select 'Selects next emtpy (non used) row

Regards
Steve

Hi Steve,

Thanks for those steps they do help - but after selecting the used range how do insert those rows in the "data" workbook is there some code you could post for that please ?

Regards

After your copy command if Data is already open

.copy Workbooks("Data").Sheets("Sheet1").Range("A" & rows.count).End(3)(2)

Using John's code line from above reply this may work for you if you change file path for "Data" workbook and worksheet name

With ActiveSheet.UsedRange
 .Offset(1, 0).Resize(.Rows.Count - 1).Select 'Selects used range in active worksheet minus row 1 using "offset"
 Selection.Copy ' Copies selected range
 Application.Workbooks.Open ("C:\Users\hartwes1\Desktop\Data.xlsx") 'Open workbook
 .Copy Workbooks("Data").Sheets("Sheet1").Range("A" & Rows.Count).End(3)(2)
End With

Hi John

If I rename the sheet name everytime i use the macrowith yesterdays date isnt that going to be a problem in that coding ?

Originally Posted by silverxx12

Hi John

If I rename the sheet name everytime i use the macrowith yesterdays date isnt that going to be a problem in that coding ?

Yes as it stands now, but it can be modified depending on how you enter the date

Sub silverxxx12()
Dim x As String
x = Format(DateAdd("d", -1, Date), "yyyymmdd")
Workbooks("Data").Sheets(x).Range("A" & Rows.Count).End(3)(2).Select
End Sub

John,

If you dont mind, is the code I enetred ok? it seems to work but wouldnt want to lead someone down the garden path so to speak!

Originally Posted by Steve@TRW

John,

If you dont mind, is the code I enetred ok? it seems to work but wouldnt want to lead someone down the garden path so to speak!

Steve, your code seems fine too me. It should work just as well.

Ok

I am using part of both of your codes as below:

 With ActiveSheet.UsedRange
    .Offset(1, 0).Resize(.Rows.Count - 1).Select 'Selects used range in active worksheet minus row 1 using "offset"
    Selection.Copy ' Copies selected range
    Application.Workbooks.Open ("C:\Users\silver\Desktop\data.xls")
    
    'remove autofilter'
   
    If ActiveSheet.AutoFilterMode = True Then
    ActiveSheet.AutoFilterMode = False
    End If
    
    .Copy Workbooks("data").Sheets(ActiveSheet.Name).Range("A" & Rows.Count).End(3)(2) '<--------ActiveSheet.Name on this line

The problems i am faced with are as follows :

the header row from the temporary data sheet seems to be copied as well - how do i get rid of that i only wish to copy from rows 2 down?

Also i want to insert the copied cells not paste them after the last row of data as the workbook has formulas at the bottom -

Thanks really appreciate the help

so i really i need to copy the data open the workbook

can you attach a copy of temp workbook?

to clarify which row are your headers on ?