copy data from workbook1 sheet1 to workbook2 sheet1,sheet2...

[Simi]

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.

'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

this string works within the same workbook provided there is a W-I-P sheet in the same workbook as newSheetName
Worksheets(newSheetName).Cells(1, cellCount) = Worksheets("W-I-P").Cells(1, cellCount)

[Ken Puls]

Sorry for the late reply here.

Why not just copy/paste it in one go?

    Workbooks(wipWorkbook).Worksheets("W-I-P").Range("A1:A40").Copy
    Worksheets(newSheetName).PasteSpecial Paste:=xlPasteAll

[Simi]

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

[Simi]

for this example Ken. lets use real names instead of variables, as in I'm copying from Book1.xlsm sheet1 to Book2.xlsm sheet4.

[Ken Puls]

Oh, my bad... I missed a cell reference on the paste. Modify it to this and give it a go:

 Workbooks(wipWorkbook).Worksheets("W-I-P").Range("A1:A40").Copy
    Worksheets(newSheetName).Range("A1").PasteSpecial Paste:=xlPasteAll

Make sure you make a backup copy of your workbook first though, just to make sure you don't overwrite something important.

If this doesn't work we'll start digging deeper into names and such.

[Simi]

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

[Ken Puls]

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!