VBA edit help - copy data from an excel without opening the file first

[TollemG]

I wrote the below script to move business commentary from a fixed sharepoint location into a master file. I was wondering if there is any way to adjust the code to not have to open the file im taking the commentary from?? Thanks

Please Login or Register  to view this content.

[john55]

Hi ,
According to the forum rules, you need to add code tags of your posted code! Please!
...I am not sure if I understand very well what you need, you want to copy two ranges and paste'em in the active workbook, is this correct?!
try this, change the apth and sheet name for your needs

Please Login or Register  to view this content.

[TollemG]

thanks for the reply john, a couple of questions as im still very new to VBA.

Please Login or Register  to view this content.

what is the "sheet(1)" supposed to represent? and what is the "sheet1" supposed to represent? Is this the tab in the workbook im pulling the data range from or is this the destination tab? I think I did something wrong because as I updated your code I get a subscript out of range error. I believe this means im trying to reference a tab that doesn't exist. This is what I filled in

Please Login or Register  to view this content.

Even with the run time error this code does the exact thing my original code did.

Please Login or Register  to view this content.

Is there no way to reference this location and pull the data without opening the workbook? If this is not possible I think I can just add something to close the file again after the paste is done.

[john55]

Hi,

Please Login or Register  to view this content.

Look at the sheet name where you want to import data and change it in the code, see red color
here

Please Login or Register  to view this content.

also, look at the sheet name wherw you want to paste the data-see the red color

Please Login or Register  to view this content.

both of them are named "BU template"?
the code opens the workbook where you take data from, copies and closes it.

[TollemG]

Bear with me John, im clearly slow, but determined to check off the vba competency box in 2017.

Please Login or Register  to view this content.

currently looks like this, the "BU template" is the sheet in the workbook im exporting data from, the "BU template - EM" is where im pasting the data into.

Something is still named incorrectly because it does not let me finish out the script to close the EM.xlsx file opened to extract the information.

[john55]

first mistake, is missing teh range

Please Login or Register  to view this content.

check the sheet name, including any space...
you mean is the wb still open after the coping data?

*** add the code tags to the 1st post, please!

[TollemG]

correct, it doesn't close after copying data.

Please Login or Register  to view this content.

this is the line that debugger identifies as error. Am I incorrectly including "" or () around sheet names? the names are pasted right out of my workbooks so spaces are correct.

Please Login or Register  to view this content.

does the code already know that the "wb" referenced is the one identified at the start of the code?

Please Login or Register  to view this content.

[john55]

Please Login or Register  to view this content.

'' you have to add'em!
yes, thw wb is set and the code knows about it! do not worry!
perhaps someone else can help us.....

[TollemG]

Ok this is driving me crazy, spent like 2 hours trying to get this to work.

I have a code that works like the one @john55 posted that I set up using simple record commands seen below

Please Login or Register  to view this content.

however, I am determined to figure out why this one will not work

Please Login or Register  to view this content.

My understanding is that the code application works as follows, set current sheet as active, open a separate workbook from the sharepoint location shown, take cell range d42:v44 and d47:v51 on the tab called "BU template" in the sharepoint document. Copy and paste these values into d42:v42 and d47:v47 in the active tab ("BU template - EM") in my original workbook. it will then close the sharepoint workbook.

[Richard Buttrey]

Where does it fail?
Are you sure you have a sheet named "BU template - EM" in 'Thisworkbook'

[TollemG]

it highlights the entire line here

Please Login or Register  to view this content.

and "thisworkbook" would refer to the location Im pasting the data into correct? yes I have a tab with that exact name.