Macro to copy data from unsaved workbook

[SubwAy]

Hi folks,

I found the macro below in this forum and have adapted it to copy some data that I extract from a sharepoint into an unsaved workbook to a file (that has the same column heading) that is permanently saved on a network drive.

The unsaved workbook has one sheet called "owssvr" and the permanent workbook has multiple sheets of which "Raw Data" is the one that is supposed to be imported into. I have tried to open the unsaved file first and permanent second and the other way around but to no avail. I keep getting a "subscript out of range" error. Can someone recommend a clever solution ?

Please Login or Register  to view this content.

[Norie]

Where do you get the error?

[humdingaling]

where does the error occur?

insert this after you set x and y

Please Login or Register  to view this content.

what do you get?

[SubwAy]

So adding this code didn't give me additional info, but by stepping through the macro manually via F8, the error comes up on the line

Please Login or Register  to view this content.

I got it to work yesterday and am obviously doing something different today but can't figure out what.

[humdingaling]

So adding this code didn't give me additional info

Press CTRL+G in VBE environment
it will open window called immediate..this is where debug information shows

when you open file from sharepoint does it open in the same session as your other workbooks?

ie go to VIEW>switch windows
do you see all the other windows there or only the current file

as you set workbook(2)...assuming you have personal.xlsb
the first file you open is workbook 2

[SubwAy]

So when I open the temporary workbook first and then the one that has the macro, I get the following in the immediate window:

CAP Extract - Master.xlsm
CAP Extract - Master.xlsm

If I open the permanent file first and then the temporary, I get this:

Book1
CAP Extract - Master.xlsm

And yes both workbooks open in the same session (office 365 if it makes a difference)

[humdingaling]

ok..on your system it doesnt count personal.xlsb
so it should in theory work when you open the permanent file first

so when testing always use this opening sequence

with regards to finding the error then
can you add this before your copy line and advise what it spits out

Please Login or Register  to view this content.

ps suggest you leave the previous debug in there
so you should get
Book1
CAP Extract - Master.xlsm
#some number representing number of rows

[SubwAy]

I get the following:

Book1
CAP Extract - Master.xlsm
3922

3922 is the number of row I want to paste today, but this number may increase of decrease over time.

[humdingaling]

try this line instead

Please Login or Register  to view this content.

[SubwAy]

humdingaling, I have no idea how this is any different but you're a legend. It works perfectly so thank you VERY MUCH!

I will now be applying this to another workbook where this time I import the content of two spreadsheets (from one temporary workbook) into another permanent worbook.

Will it be simply a matter of duplicating the copy and paste lines like this:

Please Login or Register  to view this content.

Finally, is there a way to clear the contents from line 2 downwards to make sure the new "paste" replaces everything (in case there are less lines in the new data dump)?

[humdingaling]

believe the error was because it was looking for sheet "owssvr" on the wrong workbook
putting that extra X in there makes it refer to the correct workbook with the correct sheet name

Will it be simply a matter of duplicating the copy and paste lines like this:

as long as the x and y are set correctly....this looks fine

Finally, is there a way to clear the contents from line 2 downwards to make sure the new "paste" replaces everything (in case there are less lines in the new data dump)?

clear it all out before you paste

Please Login or Register  to view this content.

something like this should work
replace 10000 with number you think larger than you will need

[SubwAy]

Thanks for taking the time to explain and respond

Can I make the ClearContents like this?

Please Login or Register  to view this content.

[humdingaling]

probably like this

Please Login or Register  to view this content.

[SubwAy]

Thank you

[SubwAy]

This not a problem but more a question for my personal interest. When I try to put the clearcontents in-between the copy and paste actions, I get an out of range error when the code needs to execute the paste.

By putting the clear contents at the beginning, everything works fine. Is there a particular rationale behind this?

Post Edit: Small error in clear contents

Can anyone tell me why the following would also clear the content of line2:

Please Login or Register  to view this content.

It works fine on everything below but I would have thought line 2 would get cleared

[humdingaling]

it should work both ways but it may be something that inherently is happening that i cannot see without the actual file to debug

debug this line to see what it gives you

Please Login or Register  to view this content.

it seems you havent got the hang of debugging just yet
http://www.excel-easy.com/vba/examples/debugging.html
http://www.cpearson.com/excel/DebuggingVBA.aspx
http://www.techonthenet.com/excel/ma..._debug2013.php

here is some reading material on how to get started
it will help you figure things out and in doing so will generally elude to the issue

[SubwAy]

Thanks for the material, it was very insightful. And you were right, I had barely no understanding of the debug line.

With regards to the lines being incorrectly cleared, I worked out that it only happened if the selection to clear was already empty.

Anyway, all my problems are resolved so thank you again for your help. I'll keep doing trials and errors so I can learn more and more code gradually

[humdingaling]

not a problem
debugging is an important part of coding its can break things down step by step
so its good to pick up early while learning

there is always plenty of learning to do, i am still always learning myself

[SubwAy]

If anyone is still seeing this thread, can someone point me in the right direction to now change this macro (for another workbook) and have it point towards a saved file (which would be open at the time) to perform the same type of import?

[fhl2009]

Hello Everyone!

Need help on this..


I am trying to copy unsaved worksheet (placed as workbook #2) that has random filename generated from a database program into workbook #1.
Workbook #3 file here has the VBA command.


Public Sub CopyPaste()

'To copy data from second temporary worksheets

Workbooks(2).Worksheets(1).Cells.Copy
Workbooks(1).Activate
Range("A1").Select
ActiveSheet.Paste
Workbooks(2).Activate
Application.CutCopyMode = False
Workbooks(3).Close SaveChanges:=False
Workbooks(2).Close SaveChanges:=False

End Sub

Still failed to copy workbook #2 content. VBA is copying workbook #3 instead of #2.

Really appreciate for anyone's help.