Copy paste data from a worksheet to a master sheet in a different workbook

[Macro2016]

Hi,

I have a set of data in a workbook named "Reports.xlsm". Please refer to the table below for details.

Provider	AAAA
Date	5/11/2016
Device 1
S.No	Type	Content Name	Status	Notes	Tester	Link
1	abc	123	Pass		xyz
2	def	456	fail		xxx
3	ghi	789	Pass		yyy
4	jkl	000	Pass		zzz

<tbody>
</tbody>

Now I want all these data to be pasted in a new workbook called "Count.xlsm" under the sheet name "Master Sheet". The above sheet is basically a template and we will be working on this sheet everyday after deleting the previous day's contents. So, for tracking the work we've done for a week, we need to consolidate the data mentioned above. Please refer to the table below for details of the master sheet.

S.No	Type	Content Name	Status	Notes	Tester	Link	Provider's Name	Device Name	Date
1	abc	123	Pass		xyz		AAAA	Device 1	5/11/2016
2	def	456	fail		xxx		AAAA	Device 1	5/11/2016
3	ghi	789	Pass		yyy		AAAA	Device 1	5/11/2016

<tbody>
</tbody>

This is how I want the master sheet to look. I was able to create a macro to copy paste the contents from the "Reports.xlsm" workbook to the next available blank row in the "Master sheet", but I don't know how to paste the Provider's name, Date and Device name for all the rows that have content in them in the master sheet (Please note that there is just one cell that contains the provider's name, date and device name in "Reports.xlsm" and I want these to be pasted for all the rows that contain content in the master sheet). It would be really helpful if you could help me with this. Thank you so much. It really means a lot!

 

[Anthony47]

Try exploiting the following code:

Dim myCRows As Long, myNext As Long

Workbooks("Reports.xlsm").Activate
Sheets("???NameOfTheSheet").Select
myNext = Workbooks("Count.xlsm").Sheets("Master Sheet").Cells(Rows.Count, 1).End(xlUp).Row + 1
myCRows = Cells(Rows.Count, 1).End(xlUp).Row - 4
Range("A5").Resize(myCRows, 7).Value
Workbooks("Count.xlsm").Sheets("Master Sheet").Cells(myNext, 1).Resize(myCRows, 7).Value = _
    Range("A5").Resize(myCRows, 7).Value
Workbooks("Count.xlsm").Sheets("Master Sheet").Cells(myNext, 8).Resize(myCRows, 1).Value = Range("B1").Value
Workbooks("Count.xlsm").Sheets("Master Sheet").Cells(myNext, 9).Resize(myCRows, 1).Value = Range("A3").Value
Workbooks("Count.xlsm").Sheets("Master Sheet").Cells(myNext, 10).Resize(myCRows, 1).Value = Range("B2").Value
'Workbooks("Reports.xlsm").Close savechanges:=False        'See Note

Put the sesulting macro in a standard module of workbook "Count.xlsm".
I assumes that the data you showed starts in A1.

Note: The line that closes "Reports.xlsm" is disabled; after testing the macro in your environment you can remove the apostrophe at the beginning of the line, so that the copied file will be closed without changes.

Bye

 

[Macro2016]

Hi Anthony47,

Thank you so much for your reply I tried executing the code. But it shows an error at the line Range("A5").Resize(myCRows, 7).Value and the error message says "Compile error: Invalid use of property". It highlights the text ".Value". Can you please tell me how to go about this? I'm not sure if I've missed something

 

[Anthony47]

Of course it is the line just before Workbooks("Count.xlsm").etc etc...
Well, I simply don't know why that line is listed in that position

Just delete that line and try again.

Bye

 

[Macro2016]

That's working perfect! Thank you so much Anthony47!!

 

[Macro2016]

Hi Anthony47,

I need a small change in the Macro. Is it possible to trigger the Macro from the sheet "Reports.xlsm" instead of "Count.xlsm"?? I want all the Macros to be run from "Reports.xlsm".
"Count.xlsm" will only be storing the data.

 

[Anthony47]

The code I provided is "neutral", ie the macro can be inserted either in the workbook "Reports.xlsm" or in the workbook "Count.xlsm".
Did you try?

Bye

 

[Macro2016]

Yayyyy!! It does work Anthony47!! Thank you so much! You're literally my savior!! ^_^ I still need a small tweak in the code..I tried my best but I wasn't able to solve it since I'm pretty new to VBA. Instead of copying all the cells in the sheet (I mean from the range "A5"), I want the macro to copy only the range "D13:J32".. The rest of the procedures are the same.. Is this possible?? Once again, sorry for troubling you and thank you so very much for the timely help!!

 

[Anthony47]

Glad to know it helped you...

As far as the new request, you should replace these lines
Workbooks("Count.xlsm").Sheets("Master Sheet").Cells(myNext, 1).Resize(myCRows, 7).Value = _
Range("A5").Resize(myCRows, 7).Value
with

Workbooks("Count.xlsm").Sheets("Master Sheet").Cells(myNext, 1).Resize(20, 7).Value = _
    Range("D13").Resize(20, 7).Value

Bye

 

[Macro2016]

Hi Anthony47,

The code works perfect! Thank you so much ^_^ But I have one problem.. The provider name, date and device name (namely the cells B1, A3 and B2) in the "Report" workbook are being pasted extra. I have a total of 60 cells that have content in the main sheet but I will be pasting only 20 of them("D13:J32") in the master sheet..Is there a way to make the values of the cells B1, B3 and A2 be pasted only 20 times (Since the range is "D13:J32")??If that's done my report would be perfect and complete. Sorry for the missing this part in the previous reply. Thanks again