CODEWITHSUNDEEP
excelvba
Geographos
Geographos

Reputation: 1456

VBA Excel - copy from multiple various ranges at once and paste in different cell ranges of newly created workbook

I would like to copy a few separate sources of data from the "old" workbook and paste them into the newly created workbook.

enter image description here

As you can see I have 6 sources of data, whereas the first one is a range of cells and remaining 5 refer just to the individual cells.

I need all of them in the places indicated in the new workbook (and sheet).

So far I've managed to copy just the first one. The second one throws error: Method 'PasteSpecial' of object' _Worksheet' failed.

Sub Generator1()
Dim wkd As Workbook, mwkt As Workbook
Dim ms As Worksheet, tw As Worksheet
Dim asbrng As Range, pianoi1 As Range, pianoi2 As Range, pianoi3 As Range, pianoi4 As Range, 
asset As Range


Set mwkt = ThisWorkbook
Set tw = mwkt.Sheets("Build Complete Photos1")

Set asbrng = tw.Range("M15:P41")
Set pianoi1 = tw.Range("F121")
Set pianoi2 = tw.Range("F122")
Set pianoi3 = tw.Range("F123")
Set pianoi4 = tw.Range("F124")
Set asset = tw.Range("E129")


asbrng.Copy
pianoi1.Copy

Set wkd = Workbooks.Add

With wkd
Application.DisplayAlerts = False
'SaveAs.Filename:="test"
Sheets("Sheet1").Name = "1"
.Sheets("1").HPageBreaks.Add Before:=Worksheets("1").Rows(42)
.Sheets("1").VPageBreaks.Add Before:=Worksheets("1").Columns(13)
Application.DisplayAlerts = True
Set ms = .Sheets("1")
With ms
'PROVIDING DATA FROM MAJOR ASBUILT DOCUMENT
    .Range("I3:L29").Select
    .Paste
    .Range("F36").Select
    **.PasteSpecial xlPasteValues**

If I use .Paste instead of .PasteSpecial xlPasteValues only formula is pasted.

Is there any way of pasting separate ranges to the new workbook at once?

Upvotes: 0

Views: 170

Answers (3)

taller
taller

Reputation: 18778

If there are two lines of Copy, the first one is overwrote by the second one.

asbrng.Copy
pianoi1.Copy

The area 1 has merged cell, you will get the data with format on new sheet with coping. If I were right, you just need the value of area 2~6. Assigning cells value is more efficient than copy/past.

Sub Generator1()
    Dim wkd As Workbook, mwkt As Workbook
    Dim ms As Worksheet, tw As Worksheet
    Dim asbrng As Range, pianoi1 As Range, asset As Range
    Set mwkt = ThisWorkbook
    Set tw = mwkt.Sheets("Build Complete Photos1")
    Set asbrng = tw.Range("M15:P41")
    Set pianoi1 = tw.Range("F121")
    Set asset = tw.Range("E129")
    Set wkd = Workbooks.Add
    Set ms = ActiveSheet
    With ms
        Application.DisplayAlerts = False
        .Name = "1"
        .HPageBreaks.Add Before:=.Rows(42)
        .VPageBreaks.Add Before:=.Columns(13)
        Application.DisplayAlerts = True
        asbrng.Copy .Range("I3")
        .Range("F36").Resize(4, 1).Value = pianoi1.Resize(4, 1).Value
        .Range("B2").Value = ass.Value ' not sure if it is B2 or B1
    End With
    'wkd.SaveAs.Filename:="test"
End Sub

Upvotes: 0

JohnM
JohnM

Reputation: 3350

The reason for the error is that you are mixing up the PasteSpecial for a Range object (see Range.PasteSpecial) and that for a Worksheet object (see Worksheet.PasteSpecial). The Worksheet one does not take an xlPasteValues value (which is what xlPasteValues is).

I have re-written your Generator1 Sub as follows:

Sub Generator1()
    Dim tw As Worksheet
    Set tw = ThisWorkbook.Sheets("Build Complete Photos1")
    
    Dim wkd As Workbook
    Set wkd = Workbooks.Add
    Application.DisplayAlerts = False
    Dim wks1 As Worksheet
    Set wks1 = wkd.Worksheets(1)
    wks1.Name = "1"
    wks1.HPageBreaks.Add Before:=Worksheets("1").Rows(42)
    wks1.VPageBreaks.Add Before:=Worksheets("1").Columns(13)
    Application.DisplayAlerts = True
    
    tw.Range("M15:P41").Copy
    wks1.Range("I3:L29").PasteSpecial xlPasteAll

    wks1.Range("F36").Value = tw.Range("F121").Value
    wks1.Range("F37").Value = tw.Range("F122").Value
    wks1.Range("F38").Value = tw.Range("F123").Value
    wks1.Range("F39").Value = tw.Range("F124").Value
    wks1.Range("E1").Value = tw.Range("E129").Value
End Sub

It uses copy/paste for the first 'copy' so that formatting, formulas etc are copied across (this is what you seem to want for this first copy?) whereas all subsequent 'copies' are done by directly setting values in the relevant cells (ie they are done without using the clipboard).

Please check the cell references that I have used (especially "E1") as I tried to interpret them from the images you attached to your question.

Upvotes: 2

Seb358
Seb358

Reputation: 33

I think the paste special has to be after the cell reference so you don't have to .select it

.Range("F36").PasteSpecial xlPasteValues

maybe try that? I'm not 100% myself but it might work as that is what I've had to do in the past when using paste special

Upvotes: 0

Related Questions