Solved: Ammending a Copy/Paste between two workbooks macro

[Incognitus]

Hi,

I'm currently using a macro to copy and paste between two workbooks, one open and one not. The macro in itself works fine enough however there are a few adjustments that need to be made for it to be outstanding.

I tried several other macros, but using this method is the only one that doesn't give me "Object Errors", so I'm hoping it will be possible to continue using it.


Range("B19:I19").Copy Destination:=wb.Sheets("XC").Range("A" & Rows.Count).End(xlUp).Offset(1)


1.) How do I amend this to paste values?
2.) Currently, the range is one "row" within a small table on a worksheet with multiple small tables. Is it possible for it to instead be a selection of rows x big, but to only copy a variant number of rows between 0 - X rows dependent on how many entries are filled in on the B column?

Any help would be much appreciated.

[PAB]

Hi Incognitus,

Perhaps if you posted the rest of your code it might make it a bit clearer.

Regards,
PAB

[Teeroy]

Hi Incognitus,
1) Pasting values (any paste special operation) needs 2 steps
Range("B19:I19").Copy
wb.Sheets("XC").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
2) You could do what you suggest by building a range with the union method but since you'd have to loop through col B to check the values you may as well copy line by line. If the order isn't important you could sort first and copy one continuous range.

[Incognitus]

For full reference, here's the code.

Dim wb As Workbook, wb2 As Workbook
Set wb2 = ActiveWorkbook
Set wb = Workbooks.Open("C:\SYS.xls")
wb2.Activate
Range("B19:I19").Copy Destination:=wb.Sheets("SYS").Range("A" & Rows.Count).End(xlUp).Offset(1)
wb.Close savechanges:=True

wb2.Activate
Range("D19:I9").ClearContents
End Sub


I tried using that line you provided Teeray as a substitution but unfortunately it didn't work.

However, I've realized that based on the information in B & C19, it might be more practically to get the information programically. (For reference, B19 is Date/Time) and C19 is a value copied from another cell).

I'll see how this goes tomorrow at work...

[Teeroy]

Hi Incognitus,
You need to have the paste workbook active, not the copy from, for the pastespecial to work (I have no idea why :dunno ). I would also change activeworkbook to thisworkbook in your code as it defines the workbook with the running code module (which workbook is the activeworkbook can sometimes surprise you). Using your code sample with pastespecial gives:

Sub test()

Dim wb As Workbook, wb2 As Workbook
Set wb2 = ThisWorkbook
Set wb = Workbooks.Open("C:\SYS.xls")

wb.Activate
wb2.Sheets("Sheet1").Range("B19:I19").Copy
wb.Sheets("SYS").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

wb.Close savechanges:=True

wb2.Sheets("sheet1").Range("D19:I9").ClearContents
End Sub

[Incognitus]

Thank Teeroy!

That accomplishes exactly what I need it to do.