VB for copying and pasting multiple worksheets

**blackhorse** · 07-12-2011, 05:02 AM

Hi all
I have two excelworkbooks, the first one is a master excel file and the second is an output version from cognos, i need to copy all the rows with data in them from each worksheet from the second workbook. The second workbook will have a variable number of worksheets each month, each tab in this worksheet will be named as 1_1,1_2,1_3 etc. The code needs to copy columns form A to H from each worksheet in the secondary file and paste it into one worksheet in the master file, so i would end up with one sheet in the excel master file with the data from all the worksheets in the second file in one continous unbroken page of data. Can anyone help.....please

**JBeaucaire** · 07-12-2011, 10:02 AM

Seems simple enough.

Please Login or Register  to view this content.

Alternate copy code:

Please Login or Register  to view this content.

**blackhorse** · 07-13-2011, 03:41 AM

thanks, but sorry myvb skills are not very good, but if the master file, (ie) the file which needs the data copied into is called "DocumentMaster" and the tab is called "alpha", with the file to be copied from is called "epsilom" and the tabs within this file are called "1_1", "1-2", "1_3" etc (These can be a variable number, they may go up to 6 tabs or 12), where would these fit in with regards to the code you have kindly outlined.....please help..kind regards

**JBeaucaire** · 07-13-2011, 10:05 AM

1) This macro goes IN your DocumentMaster workbook in a regular code module (Module1, Module2, etc.)

2) Have you read through the code? I noted the "target sheet" quite clearly in the comments. Edit that sheet name to "alpha" if that's the name of the target sheet.

3) Have you read through the code? Or tried running it once to see what it does? The macro prompts you to select a file from your hard drive to import from. You can edit the macro so the "default path to look in" is where you expect that file will already be, should save you mouse clicks.

Make sure the Epsilom workbook is closed, run the macro and select that file from the hard drive. The macro will open the file, import every sheet to your "target sheet" and then close the import file.

PLEASE read through the code carefully, line by line, several times until it's clear to you what is going on in the code. Then ask specific questions about the code if needed.

**blackhorse** · 07-13-2011, 11:40 AM

ok thankyou i will, thanks for the input...........kind regards

**JBeaucaire** · 07-13-2011, 08:13 PM

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

**blackhorse** · 07-14-2011, 04:39 AM

Hi, after running the code am getting a run time error and the compiler is highlighting the following section of ther code.????????????

ws.Range("A1", Range("H1").End(xlDown)).Copy _
wsDest.Range("A" & wsDest.Rows.Count).End(xlUp).Offset(1)

**blackhorse** · 07-14-2011, 06:15 AM

am getting 1004 error: "method range of object failed"

**JBeaucaire** · 07-14-2011, 03:55 PM

Let's see the misbehaving code in the misbehaving workbook. Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

**blackhorse** · 07-15-2011, 03:40 PM

Operation:

Ok we have two workbooks

-Master ANALYSIS Report (Destination WorkSheet called “Data”)

-Standard Barclays Report( Report downloaded form the reporting database)

The macro will be stored in the “Master ANALYSIS Report”, once triggered it will prompt the user to open the “Standard Barclays Report”(Downloaded and stored on the desktop)

Rememeber the “Standard Barclays Report” will contain a VARIABLE number of sheets, as shown the attached file. Each sheet will follow a similar naming convention as designated by the reporting database, which would “Page1_1, Page1_2 etc

The Macro must copy the first page of the worksheet in the “Standard Barclays Report” which will be “Page1_1”, it must copy the HEADERS AS WELL, Location No, Property Code, Cost Centre Code etc. However when it copys all subsequent sheets it must just copy the data and NOT the Headers.

The Macro must then paste this data in the “Master Analysis Report” in the “Data” Worksheet. So once the macro has done its work, we will end up with the “Data” tab pasted with all the data from the “Standard Barclays Report” tabs in one continous stream with no gaps and just one HEADER ROW at the top of the DATA worksheet tab. I ahve paste dthe data from the two sample sheets into the “Data” Tab in the “Master Analysis Report” so you know how the data should look once done.

The “Standard Barclays Report” tabs normally have around 65000 lines of data on each tab.
Thankyou sir

**JBeaucaire** · 07-16-2011, 04:24 AM

Like so, a couple minor tweaks...

Please Login or Register  to view this content.

**blackhorse** · 07-18-2011, 04:32 AM

Thankyou sir.........

VB for copying and pasting multiple worksheets

LinkBack

Thread Tools

Rate This Thread

Display

VB for copying and pasting multiple worksheets

Re: VB for copying and pasting multiple worksheets

Re: VB for copying and pasting multiple worksheets

Re: VB for copying and pasting multiple worksheets

Re: VB for copying and pasting multiple worksheets

Re: VB for copying and pasting multiple worksheets

Re: VB for copying and pasting multiple worksheets

Re: VB for copying and pasting multiple worksheets

Re: VB for copying and pasting multiple worksheets

Re: VB for copying and pasting multiple worksheets

Re: VB for copying and pasting multiple worksheets

Re: VB for copying and pasting multiple worksheets

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions