PasteSpecial

F

Forestq

Active Member
Joined
May 9, 2010
Messages
482
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:
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
Try...

Code: 
[font=Verdana]    [color=darkblue]Dim[/color] wkbSource [color=darkblue]As[/color] Workbook
    [color=darkblue]Dim[/color] wkbDest [color=darkblue]As[/color] Workbook
    [color=darkblue]Dim[/color] wksSource [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] wksDest [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] Rng [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] LastColumn [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=darkblue]Set[/color] wkbSource = Workbooks("my_data.xlsm")  [color=green]'change the source workbook name accordingly[/color]
    [color=darkblue]Set[/color] wksSource = wkbSource.Worksheets("Sheet1")
    
    [color=darkblue]Set[/color] wkbDest = Workbooks("temp.xlsm")  [color=green]'change the destination workbook name accordingly[/color]
    [color=darkblue]Set[/color] wksDest = wkbDest.Worksheets("Sheet1")
    
    [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
        [color=darkblue]With[/color] wksDest.Range("A3")
            .PasteSpecial xlPasteAll
            .PasteSpecial xlPasteColumnWidths
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        .EntireRow.Copy
        wksDest.Range("A3").PasteSpecial xlPasteFormats
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    Application.CutCopyMode = [color=darkblue]False[/color][/font]
 
Upvote 0
Unfortunately at work I'm using Excel 2003, so I just need change name for:

Code: 
Set wkbSource = Workbooks("my_data.xls").
Also, can I do something like I had earlier:

Code: 
MsgBox "Open file with data"
Application.Dialogs(xlDialogOpen).Show
my_data = ActiveWorkbook.Name

Set wkbSource = my_data
Because now I don't need to know name of Workbooks (this macro I will be use to copy data not only from one file).

regards,
PvK
 
Upvote 0
Code: 
Range("A3").PasteSpecial xlPasteValues
 
Upvote 0
Forestq said:
Unfortunately at work I'm using Excel 2003, so I just need change name for:

Code: 
Set wkbSource = Workbooks("my_data.xls").
Click to expand...

Good...

Also, can I do something like I had earlier:

Code: 
MsgBox "Open file with data"
Application.Dialogs(xlDialogOpen).Show
my_data = ActiveWorkbook.Name

Set wkbSource = my_data
Because now I don't need to know name of Workbooks (this macro I will be use to copy data not only from one file).
Click to expand...

Try...

Code: 
[font=Verdana]    [color=darkblue]Dim[/color] strFile [color=darkblue]As[/color] [color=darkblue]String[/color]
    
    strFile = Application.GetOpenFilename( _
        FileFilter:="Excel Files (*.xls), *.xls)", _
        Title:="Select a File", _
        MultiSelect:=False)
        
    [color=darkblue]If[/color] strFile = "False" [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    
    [color=darkblue]Set[/color] wkbSource = Workbooks.Open(strFile)
    [color=darkblue]Set[/color] wksSource = wkbSource.Worksheets("Sheet1")
    [/font]
 
Upvote 0
Now code look like this:

Code: 
    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 wkbSource = Workbooks.Open(strFile2)
    Set wksSource = wkbSource.Worksheets("Sheet1")
 
    With wksSource
        LastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
        LastRow = .Cells(.Rows.Count, LastColumn).End(xlUp).Row
        Set Rng = Range(.Cells(1, 1), .Cells(LastRow, LastColumn))
    End With
 
    With Rng
        .Copy
        With wksDest.Range("A3")
            .PasteSpecial xlPasteAll
            .PasteSpecial xlPasteColumnWidths
        End With
        .EntireRow.Copy
        wksDest.Range("A3").PasteSpecial xlPasteFormats
    End With
 
    Application.CutCopyMode = Fals

But I get some error:

Run-time error '91':
Object variable or With block variable not set

???

:confused:
 
Upvote 0
I fixed problem, it was mistake with Set variable.

All code looks now like:
Code: 
    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 = wkbSource.Worksheets("Sheet1")
 
    With wksSource
        LastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
        LastRow = .Cells(.Rows.Count, LastColumn).End(xlUp).Row
        Set Rng = Range(.Cells(1, 1), .Cells(LastRow, LastColumn))
    End With
 
    With Rng
        .Copy
        With wksDest.Range("A3")
            .PasteSpecial xlPasteAll
            .PasteSpecial xlPasteColumnWidths
        End With
        .EntireRow.Copy
        wksDest.Range("A3").PasteSpecial xlPasteFormats
    End With
 
    Application.CutCopyMode = False

But, I have other error:

Run-time error '1004':
PasteSpecial method of Range calss failed

There is somethis wrong with ".PasteSpecial xlPasteColumnWidths".

:(
 
Upvote 0
Sorry, it looks like xlPasteColumnWidths is only available in Excel 2007 and later versions. Try replacing...

Code: 
    With Rng
        .Copy
        With wksDest.Range("A3")
            .PasteSpecial xlPasteAll
            .PasteSpecial xlPasteColumnWidths
        End With
        .EntireRow.Copy
        wksDest.Range("A3").PasteSpecial xlPasteFormats
    End With

with

Code: 
    With Rng
        .Copy
        wksDest.Range("A3").PasteSpecial xlPasteAll
        .EntireRow.Copy
        wksDest.Range("A3").PasteSpecial xlPasteFormats
        .EntireColumn.Copy
        wksDest.Range("A1").PasteSpecial xlPasteFormats
    End With
 
Upvote 0
It's paste data, but header is missing (I see header starts from "A3" but is without formats ) and the last 2 row are without formats.
 
Upvote 0
When I changed code to this:

Code: 
    With Rng
        .Copy
        wksDest.Range("A1").PasteSpecial xlPasteAll
        .EntireRow.Copy
        wksDest.Range("A1").PasteSpecial xlPasteFormats
        .EntireColumn.Copy
        wksDest.Range("A1").PasteSpecial xlPasteFormats
    End With

it's work fine. But maybe, someday I want to paste date start from A3, so if You know how to do it please tell me how can I resolve this problem.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Copy and Paste
2
Replies
14
Views
1K
Anthony47
A
J
  • Question Question
Encryption
Replies
1
Views
475
RoryA
RoryA
E
  • Question Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
538
offthelip
O
A
  • Question Question
Retain hyperlinks in table column after rangetohtml paste for Outlook Email Body table
Replies
1
Views
1K
aussiechess
A
V
  • Solved
Type mismatch when trying to find a value in another sheet for update
Replies
6
Views
454
StephenCrump
S

Forum statistics

Threads
1,224,586
Messages
6,179,730
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top