-
Oct 28th, 2017, 04:30 PM
#1
Thread Starter
New Member
Macro to copy and append values
Hi,
I am trying to execute a macro which would automatically copy values from one workbook with varying rows and same columns and append into another workbook. I am not able to do it and getting error.
Sub COPYCELL()
Dim wbk As Workbook
Dim lastRow As Long
Dim strFirstFile As String
Dim strSecondFile As String
strFirstFile = "C:\Users\Admin\Desktop\Test2.xlsx"
strSecondFile = "C:\Users\Admin\Desktop\Test1.xlsx"
Set wbk = Workbooks.Open(strFirstFile)
With wbk.Sheets("Sheet1")
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range(.Cells(2, 1), .Cells(lastRow, 1)).Copy
End With
Set wbk = Workbooks.Open(strSecondFile)
With wbk.Sheets("Sheet1")
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A" & lastRow + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End With
Application.DisplayAlerts = False
wbk.SaveAs strSecondFile
wbk.Close
Application.DisplayAlerts = True
End Sub
-
Oct 28th, 2017, 09:14 PM
#2
Re: Macro to copy and append values
try
opening and setting both workbooks to objects before copying, the clipboard likely looses the content when opening an additional workbook
if that does not help, it would be helpful to know what error you are getting and on which line
use code tags when posting code
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|