PasteSpecial

[Forestq]

Team,

I have simple macro:
- open two workbooks
- from "Sheet1" copy range to "Sheet1" second sheet.

It works fine, but when macro paste the values, height & width columns are difrent like in the source sheets.
I want to have this some format in both sheets.

My code:

MsgBox "Otowrz dokument gdzie maja zostac skopiowane dane"
Application.Dialogs(xlDialogOpen).Show
temp = ActiveWorkbook.Name
 
MsgBox "Otworz plik z danymi"
Application.Dialogs(xlDialogOpen).Show
my_data = ActiveWorkbook.Name
 
'------------------------------------------
Workbooks(my_data).Worksheets("Sheet1").Activate
x_columns = Application.WorksheetFunction.CountA(Rows(1))
y_last_cell = Cells(65536, x_columns).End(xlUp).Row
Range(Cells(1, 1), Cells(y_last_cell, x_columns)).Copy
 
'------------------------------------------
Workbooks(temp).Worksheets("Sheet1").Activate
Range("A3").PasteSpecial (xlPasteAll)

thanks for your all comments and support,

regards,
PvK

 

[Domenic]

I had offered the following...

    With Rng
        .Copy
        wksDest.Range("A3").PasteSpecial xlPasteAll
        .EntireRow.Copy
        wksDest.Range("A3").PasteSpecial xlPasteFormats
        .EntireColumn.Copy
        wksDest.Range("[COLOR="Red"]A1[/COLOR]").PasteSpecial xlPasteFormats
    End With

Notice that the first two cell references are "A3", whereas the last cell reference is "A1", not "A3". Is this what you tried?

 

[Forestq]

I want to paste all data start from cell A3 = (3,1).
Header will be in rows (3 ,x ) and the data will be below them.

When I used this code header is pasted wrong - without formats, this some problem is with two last rows.

But when I changed the code (all for A1) the code work fine.

 

[Domenic]

When I used this code header is pasted wrong - without formats, this some problem is with two last rows.

When you use which code? Can you post the exact code you're referring to?

 

[Forestq]

it's Your code:

 With Rng
        .Copy
        wksDest.Range("A3").PasteSpecial xlPasteAll
        .EntireRow.Copy
        wksDest.Range("A3").PasteSpecial xlPasteFormats
        .EntireColumn.Copy
        wksDest.Range("[COLOR=red]A1[/COLOR]").PasteSpecial xlPasteFormats
    End With

I don't know why this macro doesn't run ok

 

[Domenic]

If your headers are in Row 3, shouldn't it be as follows?

    With wksSource
        LastColumn = .Cells([COLOR="Red"]3[/COLOR], .Columns.Count).End(xlToLeft).Column
        LastRow = .Cells(.Rows.Count, LastColumn).End(xlUp).Row
        Set Rng = Range(.Cells([COLOR="Red"]3[/COLOR], 1), .Cells(LastRow, LastColumn))
    End With

 

[Forestq]

I think no.
In source file, from I'm copy data, headers are at first rows(A1) and data is belows (start frm A2).

But I want to past it starting from cell A3, so in A3 should be header and from A4 should be data.

I don't understand, why when I past the data start from A1 everything works fine, but when I change the code to paste from A3 there is some mistake :/

 

[Domenic]

When I test it on Excel 2010, it seems to work fine. How about copying all of the source worksheet, pasting all of it in the destination worksheet, and then insert two rows at the top? If so, try replacing...

[font=Verdana]    [color=darkblue]With[/color] wksSource
        LastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
        LastRow = .Cells(.Rows.Count, LastColumn).End(xlUp).Row
        [color=darkblue]Set[/color] Rng = Range(.Cells(1, 1), .Cells(LastRow, LastColumn))
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    [color=darkblue]With[/color] Rng
        .Copy
        wksDest.Range("A3").PasteSpecial xlPasteAll
        .EntireRow.Copy
        wksDest.Range("A3").PasteSpecial xlPasteFormats
        .EntireColumn.Copy
        wksDest.Range("A3").PasteSpecial xlPasteFormats
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[/font]

with

[font=Verdana]    wksSource.Cells.Copy
    wksDest.Cells.PasteSpecial
    wksDest.Rows("1:2").Insert
    [/font]

Does this help?

 

[Forestq]

work's perfect,
full colde looks now like:

    Dim wkbSource As Workbook
    Dim wkbDest As Workbook
    Dim wksSource As Worksheet
    Dim wksDest As Worksheet
    Dim Rng As Range
    Dim LastColumn As Long
    Dim LastRow As Long
 
    Dim strFile As String
 
    MsgBox "Open file with data"
    strFile = Application.GetOpenFilename( _
        FileFilter:="Excel Files (*.xls), *.xls)", _
        Title:="Select a File", _
        MultiSelect:=False)
 
    If strFile = "False" Then Exit Sub
 
    Set wkbSource = Workbooks.Open(strFile)
    Set wksSource = wkbSource.Worksheets("Sheet1")
 
    Dim strFile2 As String
 
    MsgBox "Open template"
    strFile2 = Application.GetOpenFilename( _
        FileFilter:="Excel Files (*.xls), *.xls)", _
        Title:="Select a File", _
        MultiSelect:=False)
 
    If strFile2 = "False" Then Exit Sub
 
    Set wkbDest = Workbooks.Open(strFile2)
    Set wksDest = wkbDest.Worksheets("Sheet1")
 
    wksSource.Cells.Copy
    wksDest.Cells.PasteSpecial
    wksDest.Rows("1:2").Insert
 
    Application.CutCopyMode = False

So for example, If any case, I want to past data starting from A10, what should I change?
Thanks a lot for support and understanding!!!

 

[Forestq]

Ok,
I got it!!
Thanks once again for a help

 

[Domenic]

That's great, glad you've got it. And thanks for the feedback.