Sorry for the late reply here.
Why not just copy/paste it in one go?
Code:Workbooks(wipWorkbook).Worksheets("W-I-P").Range("A1:A40").Copy Worksheets(newSheetName).PasteSpecial Paste:=xlPasteAll
I am trying to copy data from a large spreadsheet to smaller parsed out versions. And I can not seem to get the code to work properly for how I am referring to the cells.
in this example, newSheetName is the name of the active sheet, in the active workbook.
wipWorkbook is a predefined string from the user.
this string works within the same workbook provided there is a W-I-P sheet in the same workbook as newSheetNameCode:'Example 'copy the header rows from W-I-P For cellCount = 1 To 40 Worksheets(newSheetName).Cells(1, cellCount) = Workbooks(wipWorkbook).Worksheets("W-I-P").Cells(1, cellCount) Next cellCount 'End of Example
Worksheets(newSheetName).Cells(1, cellCount) = Worksheets("W-I-P").Cells(1, cellCount)
Sorry for the late reply here.
Why not just copy/paste it in one go?
Code:Workbooks(wipWorkbook).Worksheets("W-I-P").Range("A1:A40").Copy Worksheets(newSheetName).PasteSpecial Paste:=xlPasteAll
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
Ken, thank you for the response. I really like the idea of simplifying the code to copy all at once as you have.
However it is still not working properly.
I changed your code, however the 2nd line still is not working.
I get Run-time error '1004':
Application-defined or object-defined error.
As a note newSheetName is an integer, so I can increment and move from sheet to sheet and copy information as needed.
I also tried replacing the Worksheets(newSheetName) with ActiveSheet to no avail.
Workbooks(wipWorkbook & ".xlsm").Worksheets("W-I-P").Range("A1:A40").Copy Worksheets(newSheetName).PasteSpecial Paste:=xlPasteAll
for this example Ken. lets use real names instead of variables, as in I'm copying from Book1.xlsm sheet1 to Book2.xlsm sheet4.
Oh, my bad... I missed a cell reference on the paste. Modify it to this and give it a go:
Make sure you make a backup copy of your workbook first though, just to make sure you don't overwrite something important.Code:Workbooks(wipWorkbook).Worksheets("W-I-P").Range("A1:A40").Copy Worksheets(newSheetName).Range("A1").PasteSpecial Paste:=xlPasteAll
If this doesn't work we'll start digging deeper into names and such.![]()
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
That seems to have done the trick, adding in the range to start the paste.
Thank you for your help.
And thank you for doing this website so people have somewhere nice to go for advice![]()
That's what I get for writing code in the browswer and not testing it.
The site is my pleasure, believe me. Thank you for posting and helping out on some of these threads too. I really appreciate it!
Ken Puls, FCPA, FCMA, MS MVP
Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book Master Your Data for Excel and Power BI, or our new Power Query Recipe cards!
Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter
If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.
Bookmarks