VBA Copy insert into another workbook _ excel 2013

[silverxx12]

Hi

I need some help here with the following. - Using Excel 2013

I have a temp worksheet which I format some data - i will be running the macro on this workbook;

What i need to do is then go to row 2 select all the rows with data in them (i need to select rows as i have data going into column "w" and possibly further)
With that data selected i need to insert the data into another excel workbook on my c drive called "Data".

However this workbook also contains data already in it - so what i need to do is to insert the data into the last row which does not contain the data

Please please can someone help with the coding here will REALLY APPRECIATE IT!!

Please if you do post any code just preface it so i can understand what is happening at each stage - i do understand a bit of vba but that is just through trial and error with the vba editor

Regards

[Steve@Rugby]

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

[silverxx12]

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

[JOHN H. DAVIS]

After your copy command if Data is already open

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

[Steve@Rugby]

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

[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 ?

[JOHN H. DAVIS]

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

[Steve@Rugby]

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!

[JOHN H. DAVIS]

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.

[Steve@Rugby]

Just working on that (I'm a beginner myself so not as quick as others to answer!), will the workbook "Data" be open or does the macro have to open it, also what is the name of the worksheet that the data should be copied to?

[silverxx12]

Hi

there is only one sheet in that book and yes the macro will have to open it i believe unless it doesnt need to - the name of the worksheet changes so that cannot be used as a reference point

Regards

[Steve@Rugby]

To open workbook (in this example it is on my desktop)

Application.Workbooks.Open ("C:\Users\hartwes1\Desktop\Data.xlsx")

[Steve@Rugby]

if you only have 1 worksheet in the "Data" file you can use the "ActiveSheet.Name" as below. This way it doesnt matter if you keep renaming worksheet.

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")
    .Copy Workbooks("Data").Sheets(ActiveSheet.Name).Range("A" & Rows.Count).End(3)(2) '<--------ActiveSheet.Name on this line
End With

[Steve@Rugby]

Forgot to save and close "Data" workbook so added a few lines for that too.

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")
    .Copy Workbooks("Data").Sheets(ActiveSheet.Name).Range("A" & Rows.Count).End(3)(2)
End With

    Application.DisplayAlerts = False
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Application.DisplayAlerts = True

[Steve@Rugby]

Hi, dont know if you need this but added more code to automatically change worksheet name in "Data" workbook to yesterdays date when macro is run.

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")
    .Copy Workbooks("Data").Sheets(ActiveSheet.Name).Range("A" & Rows.Count).End(3)(2)
End With
    
    Dim YesterdaysDate As String
    YesterdaysDate = Format(Now - 1, "ddmmmyyyy")
    ActiveSheet.Name = YesterdaysDate
    
    Application.DisplayAlerts = False
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Application.DisplayAlerts = True

[Steve@Rugby]

It clarifies it but not sure why it does not work as it works with my workbooks, can you attach your files? What column are the formulas on, you say last row but do you really mean last row of a table or something similar?

[silverxx12]

Hi

Sorry steve i cannot attach the file atm -sorry

To explain, essentially you are adding the data to a larger list of data which has total formulas at the bottom - between the formulas and end of the list of data there are some empty rows

In the old excel 2003 one was able to copy rows and insert copied rows into another worksheet - that is not a feature in 2013 as it is NOT highlighted after copying the selection- it driving me crazy and that essentially is my problem here.

If i manually copy them and paste them whilst vba isnt on it works with vba its not highlighted :-(

[JOHN H. DAVIS]

I modified your original code to what I think you may want to achieve. See if it helps on a sample.

Sub silverxxx12zz()
Dim x As Long
Dim ws As Worksheet
Set ws = ActiveSheet
x = ActiveSheet.UsedRange.Rows.Count

Range("A2:A" & x).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
Range("A2").EntireRow.Resize(x).Insert xlDown
Range("A2:A" & x).EntireRow.Value = ws.Range("A2:A" & x).EntireRow.Value
 '   .Copy Workbooks("data").Sheets(ActiveSheet.Name).Range("A" & Rows.Count).End(3)(2) '<--------ActiveSheet.Name on this line
End Sub

[silverxx12]

thnk you so much seems to be working now

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback. Please comply with Forum Rule No. 9.

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.

[Steve@Rugby]

Sorry, I have no experience with 2013 so i doubt i will be able to help with this particular issue.

I hope someone else more qualified can help with this.