VBA to get data from closed workbook

V

Vladgs

New Member
Joined
Nov 26, 2015
Messages
45
Hi all, I know this is a topic discussed on many formus, I just can't seem to get the right solution. My VBA experience is very limited but I am looking forward to learn.
I am looking for a piece of code to copy certain columns from one closed workbook in a specific folder, over to the active workbook in a specific worksheet.
Seen dozens of examples but I can't personalize them unfortunattely

Thank you for your interest!
 
Sort by date Sort by votes

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Good morning!

I've tried the code, I've also made some changes to it adapting to my specific circumstances. Unfortunately I am still having some issues.

the error I get is "Object required" at the "LastRow = InputFile.Sheets(1).Cells(Rows.Count, "P").End(xlUp).Row"


Code: 
' Copy what you want from InputFile1:
InputFile1.Sheets(1).Activate
LastRow = InputFile.Sheets(1).Cells(Rows.Count, "P").End(xlUp).Row
InputFile1.Sheets("Sheet1").Range("P1:S" & LastRow).Copy

I also tried the following but with no success

Code: 
Set sht = InputFile1.Sheets(1)
LastRow = sht.Cells(sht.Rows.Count, "P").End(xlUp).Row

Any ideea why this happens ?
 
Upvote 0
Vladgs said:
Good morning!

I've tried the code, I've also made some changes to it adapting to my specific circumstances. Unfortunately I am still having some issues.

the error I get is "Object required" at the "LastRow = InputFile.Sheets(1).Cells(Rows.Count, "P").End(xlUp).Row"


Code: 
' Copy what you want from InputFile1:
InputFile1.Sheets(1).Activate
LastRow = InputFile.Sheets(1).Cells(Rows.Count, "P").End(xlUp).Row
InputFile1.Sheets("Sheet1").Range("P1:S" & LastRow).Copy

I also tried the following but with no success

Code: 
Set sht = InputFile1.Sheets(1)
LastRow = sht.Cells(sht.Rows.Count, "P").End(xlUp).Row

Any ideea why this happens ?
Click to expand...
Is the sheet called Sheet1 or just 1? Either way it looks like you are calling it 2 different things in your modified code.
 
Upvote 0
This is my code so far:

Code: 
Sub CopyPasteWrkBk()
' Defines variables
Dim InputFile1 As Workbook
Dim InputFile2 As Workbook
Dim OutputFile As Workbook

' LastRow of Template Data sheet
LR = ThisWorkbook.Sheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
' Open input / output workbooks:
Set InputFile1 = Workbooks.Open(Sheets("sheet2").Range("K1").Value)


ThisWorkbook.Activate

Set InputFile2 = Workbooks.Open(Sheets("sheet2").Range("L1").Value)
Set OutputFile = ThisWorkbook

' Copy what you want from InputFile1:
InputFile1.Sheets(1).Activate
LastRow = InputFile1.Sheets(1).Cells(Rows.Count, "P").End(xlUp).Row
InputFile1.Sheets(1).Range("P1:S" & LastRow).Copy


' Paste to OutputFile worksheet:
OutputFile.Sheets("Sheet2").Activate
OutputFile.Sheets("Sheet2").Range("A1").PasteSpecial xlPasteValues

' Increase LR by 1 to account for paste
LR = LR + 1

' Copy what you want from InputFile2:
InputFile2.Sheets(1).Activate
LastRow = InputFile2.Sheets(1).Cells(Rows.Count, "AP").End(xlUp).Row
InputFile2.Sheets(1).Range("AP2:AS" & LastRow).Copy


' Paste to OutputFile worksheet:
OutputFile.Sheets("Sheet2").Activate
OutputFile.Sheets("Sheet2").Range(LR & "A").PasteSpecial xlPasteValues


' Increase LR by 1 to account for paste
LR = LR + 1

' Close InputFiles
InputFile1.Close
InputFile2.Close

End Sub


The sheet in the output file is called "Sheet2"
the sheets in the input files have duifferent names but i've learned that I can refer to them by theyr number using "Sheet(1)"

I'm trying to copy columns P to S from the first input file and AP to AS from the second one, starting at row 2.

Tweeked it some more, seems to work fine untill the last paste witch happens at row 3 instead of the first empty one.

Notice i've added 1 and 2 at the "LastRow = InputFile1.Sheets(1).Cells(Rows.Count, "P").End(xlUp).Row " line. don't know if this is right.
 
Upvote 0
Vladgs said:
This is my code so far:

Code: 
Sub CopyPasteWrkBk()
' Defines variables
Dim InputFile1 As Workbook
Dim InputFile2 As Workbook
Dim OutputFile As Workbook

' LastRow of Template Data sheet
LR = ThisWorkbook.Sheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
' Open input / output workbooks:
Set InputFile1 = Workbooks.Open(Sheets("sheet2").Range("K1").Value)


ThisWorkbook.Activate

Set InputFile2 = Workbooks.Open(Sheets("sheet2").Range("L1").Value)
Set OutputFile = ThisWorkbook

' Copy what you want from InputFile1:
InputFile1.Sheets(1).Activate
LastRow = InputFile1.Sheets(1).Cells(Rows.Count, "P").End(xlUp).Row
InputFile1.Sheets(1).Range("P1:S" & LastRow).Copy


' Paste to OutputFile worksheet:
OutputFile.Sheets("Sheet2").Activate
OutputFile.Sheets("Sheet2").Range("A1").PasteSpecial xlPasteValues

' Increase LR by 1 to account for paste
LR = LR + 1

' Copy what you want from InputFile2:
InputFile2.Sheets(1).Activate
LastRow = InputFile2.Sheets(1).Cells(Rows.Count, "AP").End(xlUp).Row
InputFile2.Sheets(1).Range("AP2:AS" & LastRow).Copy


' Paste to OutputFile worksheet:
OutputFile.Sheets("Sheet2").Activate
OutputFile.Sheets("Sheet2").Range(LR & "A").PasteSpecial xlPasteValues


' Increase LR by 1 to account for paste
LR = LR + 1

' Close InputFiles
InputFile1.Close
InputFile2.Close

End Sub

...

Tweeked it some more, seems to work fine untill the last paste witch happens at row 3 instead of the first empty one.
Click to expand...
I think this is because LR equals last row +1, meaning on a blank document LR is actually row 2, and after your first paste we increased LR = LR +1.

I think we can tweak it further by changing the order in which things occur, and seeing as your first paste appears to be going into A1 regardless we can wait to set LR until the first paste in complete. Once there is data in the sheet the last row will be an actual row, so last row +1 "should" now be row 2.

Also, if you are not expecting to keep adding more lines to the workbook after this, you could ultimately remove the LR = LR +1 entirely.

Try out the updated version below.

Code: 
Sub CopyPasteWrkBk()
' Defines variables
Dim InputFile1 As Workbook
Dim InputFile2 As Workbook
Dim OutputFile As Workbook

' Open input / output workbooks:
Set InputFile1 = Workbooks.Open(Sheets("sheet2").Range("K1").Value)


ThisWorkbook.Activate

Set InputFile2 = Workbooks.Open(Sheets("sheet2").Range("L1").Value)
Set OutputFile = ThisWorkbook

' Copy what you want from InputFile1:
InputFile1.Sheets(1).Activate
LastRow = InputFile1.Sheets(1).Cells(Rows.Count, "P").End(xlUp).Row
InputFile1.Sheets(1).Range("P1:S" & LastRow).Copy


' Paste to OutputFile worksheet:
OutputFile.Sheets("Sheet2").Activate
OutputFile.Sheets("Sheet2").Range("A1").PasteSpecial xlPasteValues

' LastRow of Template Data sheet
LR = ThisWorkbook.Sheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1

' Copy what you want from InputFile2:
InputFile2.Sheets(1).Activate
LastRow = InputFile2.Sheets(1).Cells(Rows.Count, "AP").End(xlUp).Row
InputFile2.Sheets(1).Range("AP2:AS" & LastRow).Copy


' Paste to OutputFile worksheet:
OutputFile.Sheets("Sheet2").Activate
OutputFile.Sheets("Sheet2").Range("A" & LR).PasteSpecial xlPasteValues


' Increase LR by 1 to account for paste
LR = LR + 1

' Close InputFiles
InputFile1.Close
InputFile2.Close

End Sub
 
Upvote 0
Works great !

Thank you for your time and explanation!

It's encouraging I thought of the same moves but couldn't translate to VBA.

Hope I'll get here !
 
Upvote 0
Hi,

Is there a quick way to save the imput files in a different folder, using cell referance for address, and than delete the original imput files. I'd also like to keep the input file manes.
I have done it in a separate macro. Just wandering if it can be done in the copy-paste macro using the file references I allready have.

Regards,
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
428
Insurance Dave
I
R
  • Question
VBA to pull data from closed workbooks
Replies
3
Views
646
Logit
Logit
KlausW
  • Solved
copy data from several closed workbooks to one workbook.
Replies
5
Views
834
KlausW
KlausW
J
  • Question
Run time error with VBA to copy Chart objects from closed workbook
Replies
4
Views
193
Domenic
D
D
  • Question
Saving workbook "hides" macros
Replies
2
Views
460
dotsent
D

Forum statistics

Threads
1,216,583
Messages
6,131,557
Members
449,655
Latest member
Anil K Sonawane

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top