VBA Macro copy cells or range in different workbooks same worksheet name to a single master workbook with date in every row

[rozek]

Hi. I'm new to this Forum. Any help is much appreciated. The objective as per the snapshot where I need to copy & paste only for colored cells. I guess the only difference from the other posts is how to add the dates repeatedly in the Mastersheet. Need some sort of looping I guess. Appreciate any help. Thank you

 

[johnnyL]

@rozek how are those cells colored in each of those workbooks? Are they colored via conditional formatting? or some other way?

 

[rozek]

@rozek how are those cells colored in each of those workbooks? Are they colored via conditional formatting? or some other way?

Thank you johnnyL for reply. Just ignore the color. the color is just to show example the position of the value in the cell from copy to paste.

 

[johnnyL]

Ok, colors will be ignored.

Are all of the workbooks setup exactly as you described them via picture?

Do All of them have a separate date line followed by 3 lines of additional data?

Which columns and rows are the data in?

Are all of the workbooks opened when the code is ran? Is it only 3 workbooks in addition to the current workbook that contains the 'Mastersheet'?

Please be specific for what you have and what you want.

 

[rozek]

I should have made it clear. My apologies. Please see the XL2BB below. Ignore the colors (just to show where the cells position). Thank you for your help

wrkbook1.xls
	A	B	C	D
1	DATE:	4-Aug-21
2	NAME	HOURS	PRESS	TEMP
3	A	0	1290	88
4	B	0	1190	96
5	C	8	960	162
Sheet1

wrkbook2.xls
	A	B	C	D
1	DATE:	5-Aug-21
2	NAME	HOURS	PRESS	TEMP
3	A	0	1250	55
4	B	5	1000	66
5	C	24	960	77
Sheet1

wrkbook3.xls
	A	B	C	D
1	DATE:	6-Aug-21
2	NAME	HOURS	PRESS	TEMP
3	A	0	1000	65
4	B	8	900	87
5	C	4	500	140
Sheet1

The final product

wrkbook.xlsm
	A	B	C	D
1	DATE	NAME	PRESS	TEMP
2	4-Aug-21	A	1290	88
3	4-Aug-21	B	1190	96
4	4-Aug-21	C	960	162
5	5-Aug-21	A	1250	55
6	5-Aug-21	B	1000	66
7	5-Aug-21	C	960	77
8	6-Aug-21	A	1000	65
9	6-Aug-21	B	900	87
10	6-Aug-21	C	500	140
Mastersheet

 

[johnnyL]

Ok the columns/rows question was answered, how about the rest of the questions?

 

[rozek]

Ok the columns/rows question was answered, how about the rest of the questions?

Are all of the workbooks setup exactly as you described them via picture?
Yes, the setup is exactly like in XL2BB with 3 workbooks having same sheet name 'sheet1' and 1 master wrkbook.xlsm with sheet name Mastersheet.

Do All of them have a separate date line followed by 3 lines of additional data?
Yes, all source workbooks have separate date in cell B:1. When pasted, the date should be available in column A accordingly following the number of rows for that particular sheet.

Which columns and rows are the data in?
As per XL2BB snapshot

Are all of the workbooks opened when the code is ran?
Only the master workbook opens. Don't show the source workbooks.

Is it only 3 workbooks in addition to the current workbook that contains the 'Mastersheet'?
For this example yes it is only for these 3 source workbooks. May add other workbooks in the future.

Thank you again for your time and help Sir. Appreciate it alot.

 

[johnnyL]

Can the closed workbooks be opened to process the data from them?

 

[rozek]

Can the closed workbooks be opened to process the data from them?

Hi Johnny. Yes, the closed wrkbook can be open, process the data and closed afterwards when data pasted in the master workbook.

 

[johnnyL]

Another question, Are the closed workbooks all in the same folder or will each closed folder path need to be asked from the user?

In other words, do you know the path to the closed files or do you have to ask the user for each closed file?

In more other words, what is the path that the closed files are in, if you know it.