Unable to copy single cell and paste on range

Question

I'm extremely new with VBA and am learning as I go, please bear with me.

I'm trying to copy data ranges from multiple Excel files in a folder into one single consolidator. Currently in each file is an identifier of the report submitter, which I am trying to copy into a range (A3:A-lastrow). Running into errors when I reach that point.

Update 1 - Some of you have pointed out my first error - including quotes on my lastrow variable. Thank you! Have removed them but now the macro doesn't seem to be able to copy paste between my source file and destination file. Is there something wrong with how I declared the workbook variables or is it the way I am calling them?

Update 2 - After going through @Mikku answer and adjusting it a bit I'm able to safely say that the code finally works!

Sub MainCopy()

Dim SrcBk As Workbook
Dim FSO As Object
Dim Folder As Object
Dim SrcF As Object
Dim F1 As Object
Dim ws As Worksheet
Dim lastrow As Long
Dim DestWk As Worksheet

Set DestWk = ThisWorkbook.Sheets("Output")

'Define source folder
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Folder = FSO.Getfolder(ActiveWorkbook.Sheets("Cover").Range("F5"))
Set SrcF = Folder.Files

'Loop files in Directory
For Each F1 In SrcF

    lastrow = DestWk.Cells(DestWk.Rows.Count, "B").End(xlUp).Row

    ThisWorkbook.Sheets("Reference").Select
    ThisWorkbook.Sheets("Reference").Range("A3:C113").Select
    Selection.Copy
    Worksheets("Output").Select
    Range("B" & lastrow + 1).PasteSpecial xlValues


    Set SrcBk = Workbooks.Open(F1)

    Worksheets("Cover").Select
    Range("K1").Copy
    DestWk.Range("A" & DestWk.Cells(DestWk.Rows.Count, "A").End(xlUp).Row & ":" & "A" & lastrow).PasteSpecial Paste:=xlPasteValues

    SrcBk.Worksheet("Data").Range("C7:I38").Copy
    DestWk.Cells("E" & lastrow).PasteSpecial Paste:=xlPasteValues

    lastrow = DestWk.Cells(DestWk.Rows.Count, "I").End(xlUp).Row

    SrcBk.Worksheet("Data").Range("C40:I68").Copy
    DestWk.Cells("E" & lastrow).PasteSpecial Paste:=xlPasteValues

    lastrow = DestWk.Cells(DestWk.Rows.Count, "I").End(xlUp).Row

    SrcBk.Worksheet("Performance").Range("C8:I61").Copy
    DestWk.Cells("E" & lastrow).PasteSpecial Paste:=xlPasteValues

    SrcBk.Close

Next F1


End Sub

If you have the `lastrow` within quotes, it's no longer the variable, but the `String` "lastrow." Just remove the quotes. — BigBen, Jun 18 '19 at 02:52
Thanks BigBen, removed the quotes but now the macro doesn't seemt to be able to switch between my sourcefiles and destination files. Is there an issue with how I am referencing the files when I declared the variables? — Simo Keng, Jun 18 '19 at 03:47

Mikku · Accepted Answer · 2019-06-18T04:41:28.467

0

Replace this line

DestWk.Range("A" & DestWk.Cells(DestWk.Rows.Count, "A").End(xlUp).Row & ":" & "A" & "lastrow").PasteSpecial Paste:=xlPasteValues

With

DestWk.Range("A" & DestWk.Cells(DestWk.Rows.Count, "A").End(xlUp).Row & ":" & "A" & lastrow).PasteSpecial Paste:=xlPasteValues

You were using extra quotes, variables are used without quotes.

You need to Change .Worksheet with .Worksheets.... which appears almost 4 times in your code.

Also, One more suggestion ... Read this answer How to avoid using Select in VBA

Try this code: I have made few more changes.

Sub MainCopy()

Dim SrcBk As Workbook
Dim FSO As Object
Dim Folder As Object
Dim SrcF As Object
Dim F1 As Object
Dim ws As Worksheet
Dim lastrow As Long
Dim DestWk As Worksheet

Set DestWk = ThisWorkbook.Sheets("Output")

'Define source folder
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Folder = FSO.Getfolder(ActiveWorkbook.Sheets("Cover").Range("F5"))
Set SrcF = Folder.Files

'Loop files in Directory
For Each F1 In SrcF

    lastrow = DestWk.Cells(DestWk.Rows.Count, "B").End(xlUp).Row

    ThisWorkbook.Sheets("Reference").Range("A3:C113").Copy
    DestWk.Range("B" & lastrow + 1).PasteSpecial Paste:=xlPasteValues


    Set SrcBk = Workbooks.Open(F1)

    SrcBk.Worksheets("Cover").Range("K1").Copy
    DestWk.Range("A" & DestWk.Cells(DestWk.Rows.Count, "A").End(xlUp).Row & ":" & "A" & lastrow).PasteSpecial Paste:=xlPasteValues

    SrcBk.Worksheets("Data").Range("C7:I38").Copy
    DestWk.Range("E" & lastrow).PasteSpecial Paste:=xlPasteValues

    lastrow = DestWk.Cells(DestWk.Rows.Count, "I").End(xlUp).Row

    SrcBk.Worksheets("Data").Range("C40:I68").Copy
    DestWk.Range("E" & lastrow).PasteSpecial Paste:=xlPasteValues

    lastrow = DestWk.Cells(DestWk.Rows.Count, "I").End(xlUp).Row

    SrcBk.Worksheets("Performance").Range("C8:I61").Copy
    DestWk.Range("E" & lastrow).PasteSpecial Paste:=xlPasteValues

    SrcBk.Close

Next F1


End Sub

edited Jun 18 '19 at 04:41

answered Jun 18 '19 at 02:56

Mikku

6,538
3
15
38

Let us know if the code is still showing any error. This was all I could Spot. – Mikku Jun 18 '19 at 02:57
Okay that was silliness on my part, I've removed the quotes on lastrow, but the macro gets stuck when changing between the copy-file and destination-file. Is my Srcbk and Destbk references incorrect? – Simo Keng Jun 18 '19 at 03:46
On which line ?? @SimoKeng – Mikku Jun 18 '19 at 03:55
@SimoKeng .. Another Error I could spot is that you have used `.Worksheet` ... You should be using `.Worksheets` with an `s` at the end. – Mikku Jun 18 '19 at 03:59
I'm getting an "Invalid procedure call or argument" at the following line DestWk.Cells("E" & lastrow).PasteSpecial Paste:=xlPasteValues – Simo Keng Jun 18 '19 at 04:17
See that 's the Reason I wanted you to read the Answer to `Why to Avoid using Select` ... Anyways whats happening is VBA is at the Sheet("Cover") and we are trying to paste data in another sheet("Output"). To overcome this error you need to Write this line before pasting the Data, that is the line you are getting error in. `DestWk.Activate` – Mikku Jun 18 '19 at 04:22
I tried adding `DestWk.Activate` before the pastespecial line and after the copy, it's still giving me an invalid procedure call or argument error. – Simo Keng Jun 18 '19 at 04:31
Sorry @Mikku it breaks at `DestWk.Cells("E" & lastrow).PasteSpecial Paste:=xlPasteValues` – Simo Keng Jun 18 '19 at 04:37
Yes... Actually you have to change the `.Cells` to `.Range` .Cells don't take string Arguments. Range takes that. @SimoKeng – Mikku Jun 18 '19 at 04:40
Use the code in Answer Now, I have amended it. @SimoKeng ... It should work Now – Mikku Jun 18 '19 at 04:41
Apologies for not replying earlier, had a downtime and was mucked away without any internet. Your code works and I'm able to do the extraction loop now, thank you! I've refined the code a bit more because I realized that I was pasting over previous data as well, and added a few aesthetics to it :) Will post up the cleaned code later in the day. Thank you @Mikku ! – Simo Keng Jun 19 '19 at 07:57

Unable to copy single cell and paste on range

1 Answers1

Try this code: I have made few more changes.