Copying data from Multiple spreadsheet to single spreadsheet

**Cool\m/** · 05-09-2013, 05:23 AM

As the title suggest, I am trying to copy data from Multiple spreadsheet to single spreadsheet. There will be around 200-300 sheets. I did a lot of googling and found the following macro to be best.

But the issue is I have some sheets, where first column is blank for all the records.

For example I have 3 sheets like these.

1	AAA
2	BBB
3	CCC
4	DDD

5	EEE
	FFF
	GGG
	HHH

9	III
10	JJJ

When I execute the macro it is creating a final sheet like this.

1	AAA
2	BBB
3	CCC
4	DDD
5	EEE
9	III
10	JJJ

It is not copying the FFF,GGG and HHH rows. From further analysis I found that, when the macro is looking for the last cell to start pasting data for the next spreadsheet, It pointing to

FFF

Thus it is overwriting F,G and H records.

How to overcome this issue?

**AB33** · 05-09-2013, 05:26 AM

Please attach your sample. This easily can be rectified, but we need to see the format of your data. Go to advance, then attachment.

**Cool\m/** · 05-09-2013, 05:35 AM

Here is a sample file I created. The data in sheet3 are getting overwritten.

**AB33** · 05-09-2013, 05:37 AM

There is not code on the attached. Is this right?

**HaHoBe** · 05-09-2013, 05:41 AM

Hi, Cool\m/,

maybe change the check for the last entry from Column A to Column B (assuming Columns A and B from the part of spreadsheet offered, or the Column which has consistent data in all rows)?

Ciao,
Holger

**Cool\m/** · 05-09-2013, 05:47 AM

My bad, forgot to attach the macro code . Edited my first post with the code.

Here is the xls with code.

**AB33** · 05-09-2013, 05:49 AM

Please Login or Register  to view this content.

**Cool\m/** · 05-09-2013, 05:49 AM

Originally Posted by HaHoBe

Hi, Cool\m/,

maybe change the check for the last entry from Column A to Column B (assuming Columns A and B from the part of spreadsheet offered, or the Column which has consistent data in all rows)?

Ciao,
Holger

Thats where I am stuck.

This is the line which is culprit.
Range("A65536").End(xlUp).Offset(1, 0).Select

I am big noob with vb and macro.

**AB33** · 05-09-2013, 05:51 AM

The code you have attached has lots of select. Please use my code and let me know if you have any issue.

**HaHoBe** · 05-09-2013, 05:52 AM

Hi, Cool\m/,

instead of using Column A

Please Login or Register  to view this content.

use Column B like

Please Login or Register  to view this content.

although I know you would not need to select a cell for any code to work.

Ciao,
Holger

**Cool\m/** · 05-09-2013, 05:56 AM

Originally Posted by AB33

The code you have attached has lots of select. Please use my code and let me know if you have any issue.

No data is populated in Master sheet.

**AB33** · 05-09-2013, 05:58 AM

Put a header in Master List sheet in row1 and code works perfectly.

**Cool\m/** · 05-09-2013, 06:06 AM

It worked, but I want the data as it is, it will contain the filenames and column names for all the sheets. i.e , in master sheet,

Column1

Column2

Column3

will appear thrice for 3 sheets.

Also the number of records varries, in some sheets it is more than 50. And thanks for your quick response.

**AB33** · 05-09-2013, 06:24 AM

Filename: Sheet1.xls

Column1 Column2 Column3

Do you mean all data need to be copied in to master sheet, even the columns? I can understand why you want to copy the file name and sheets, but not the columns. So, basically, you want to copy everything in every sheet.

**AB33** · 05-09-2013, 06:37 AM

Please Login or Register  to view this content.

**Cool\m/** · 05-09-2013, 06:41 AM

Well column1,2,3 are not required, but it is allright if those exist, but the filenames are mandatory. So I did some modification to your code to include them as well. Please check it once.

Please Login or Register  to view this content.

Added a line to include formatting also, as some cells are merged.
Removed these two lines

ms.Range("A2:A" & Rows.Count - 1).NumberFormat = "dd/mm/yyyy"
ms.Range("C2:C" & Rows.Count - 1).NumberFormat = "#,###0.00"

Data may not be date as well in some sheets

**AB33** · 05-09-2013, 06:44 AM

See amended post#15

**Cool\m/** · 05-09-2013, 07:07 AM

Wow.. thats working exactly what I want. Thanks a lot,

Just a little change i did, as I want to copy the formatting also. added the

ms.Range("A" & NR).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

ms.Range("A" & NR).PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Will it break anything?

Anyway lot of thanks, I was stucked here from yesterday.

**AB33** · 05-09-2013, 07:17 AM

Will it break anything?
No, but it will slow down the code as the code has to copy all formats.

Could you please now close (Mark) this thread as solved? Go in to the top right-hand side of this page, choose "Thread Tools" from the menu, then select "solved" from the drop down menu.

**Cool\m/** · 05-09-2013, 07:45 AM

Done Sir.

Copying data from Multiple spreadsheet to single spreadsheet

LinkBack

Thread Tools

Rate This Thread

Display

Copying data from Multiple spreadsheet to single spreadsheet

Re: Copying data from Multiple spreadsheet to single spreadsheet

Re: Copying data from Multiple spreadsheet to single spreadsheet

Re: Copying data from Multiple spreadsheet to single spreadsheet

Re: Copying data from Multiple spreadsheet to single spreadsheet

Re: Copying data from Multiple spreadsheet to single spreadsheet

Re: Copying data from Multiple spreadsheet to single spreadsheet

Re: Copying data from Multiple spreadsheet to single spreadsheet

Re: Copying data from Multiple spreadsheet to single spreadsheet

Re: Copying data from Multiple spreadsheet to single spreadsheet

Re: Copying data from Multiple spreadsheet to single spreadsheet

Re: Copying data from Multiple spreadsheet to single spreadsheet

Re: Copying data from Multiple spreadsheet to single spreadsheet

Re: Copying data from Multiple spreadsheet to single spreadsheet

Re: Copying data from Multiple spreadsheet to single spreadsheet

Re: Copying data from Multiple spreadsheet to single spreadsheet

Re: Copying data from Multiple spreadsheet to single spreadsheet

Re: Copying data from Multiple spreadsheet to single spreadsheet

Re: Copying data from Multiple spreadsheet to single spreadsheet

Re: Copying data from Multiple spreadsheet to single spreadsheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions