Using copy Destination with a destination on a different sheet

[Hayt]

I am creating a fairly simple macro that copies data from one sheet and pastes it on another.

Worksheets("drawer cutlist").Range("B6").Select
Do Until ActiveCell.Value = ""
Sheets("Sq footage calc").Select
Rows("3:3").EntireRow.Select
Selection.Insert shift:=xlDown, copyorigin:=xlFormatFromLeftOrAbove
Range("a3").Select
ActiveCell.FormulaR1C1 = "2"
Range("E4:F4").Copy Destination:=Range("E3:F3")
Sheets("Drawer cutlist").Select
ActiveCell.Select
Selection.Copy
Sheets("Sq footage calc").Select
Range("b3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Drawer cutlist").Select
ActiveCell.Offset(0, 2).Range("A1").Select
Selection.Copy
Sheets("Sq footage calc").Select
Range("c3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("3:3").EntireRow.Select
Selection.Insert shift:=xlDown, copyorigin:=xlFormatFromLeftOrAbove
Range("a3").Select
ActiveCell.FormulaR1C1 = "2"
Range("E4:F4").Copy Destination:=Range("E3:F4")
Sheets("Drawer cutlist").Select
ActiveCell.Offset(2, -2).Copy Destination:=Sheets("sq footage calc").Range("b3").Value
ActiveCell.Offset(0, 2).Copy Destination:=Sheets("sq footage calc").Range("c3").Value
Worksheets("sq footage calc").Rows("3:3").EntireRow.Insert shift:=xlDown, copyorigin:=xlFormatFromLeftOrAbove
Worksheets("sq footage calc").Range("a3").FormulaR1C1 = "1"
Worksheets("sq footage calc").Range("e4,f4").Copy Destination:=Sheets("sq footage calc").Range("e3,f3")
ActiveCell.Offset(2, -2).Copy Destination:=Sheets("sq footage calc").Range("B3").Value
ActiveCell.Offset(0, 2).Copy Destination:=Sheets("sq footage calc").Range("B3").Value
ActiveCell.Offset(-2, 3).Select

Loop

The first part is basically what I got from recording. I tried to simplify the code by using

ActiveCell.Offset(2, -2).Copy Destination:=Sheets("sq footage calc").Range("b3").Value

but when i try to run it it stops there and gives me the error message
Run-time error '1004': copy method of range class failed.

I know I can just repeat the first part of the code to get the results I want, but I was just curious why this line doesn't work.

Thanks,
Mike

 

[pedie]

Mike, maybe someone can help better, however I think it'll be better to explain what you want to achive...your code not good

 

[Hayt]

I am very new to VBA, so thank you for your patience. I am trying to copy data from the sheet "drawer cutlist" to the sheet "sq footage calc". the data from "drawer cutlist" is in column b and d and there is a blank row between each set of data. I want the macro to copy and past the data so there are blank rows in the "sq footage calc" sheet then use a loop so it just goes until it encounters a blank cell. I recorded a macro and that does it sort of, but I replaced the off-sets on "sq footage calc". I could have just continued down that road and turned off screen updating, but i thought it would run faster if I used copy destination:=, but i don't really know how to use it. So the part of the following code in italic is the part that i adjusted from recording, and the bold is the part that doesn't work. The italic part works fine, and from there i want to select a cell two rows down and two columns to the left on the sheet "drawer cutlist" and copy and paste that to the cell B3 in "sq footage calc". I don't know if that makes any sense, but I just don't understand why my copy destination lines don't work, I have a feeling its something really simple.

Worksheets("drawer cutlist").Range("B6").Select
Do Until ActiveCell.Value = ""
Sheets("Sq footage calc").Select
Rows("3:3").EntireRow.Select
Selection.Insert shift:=xlDown, copyorigin:=xlFormatFromLeftOrAbove
Range("a3").Select
ActiveCell.FormulaR1C1 = "2"
Range("E4:F4").Copy Destination:=Range("E3:F3")
Sheets("Drawer cutlist").Select
ActiveCell.Select
Selection.Copy
Sheets("Sq footage calc").Select
Range("b3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Drawer cutlist").Select
ActiveCell.Offset(0, 2).Range("A1").Select
Selection.Copy
Sheets("Sq footage calc").Select
Range("c3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("3:3").EntireRow.Select
Selection.Insert shift:=xlDown, copyorigin:=xlFormatFromLeftOrAbove
Range("a3").Select
ActiveCell.FormulaR1C1 = "2"
Range("E4:F4").Copy Destination:=Range("E3:F4")
Sheets("Drawer cutlist").Select
ActiveCell.Offset(2, -2).Copy Destination:=Sheets("sq footage calc").Range("b3").Value
ActiveCell.Offset(0, 2).Copy Destination:=Sheets("sq footage calc").Range("c3").Value
Worksheets("sq footage calc").Rows("3:3").EntireRow.Insert shift:=xlDown, copyorigin:=xlFormatFromLeftOrAbove
Worksheets("sq footage calc").Range("a3").FormulaR1C1 = "1"
Worksheets("sq footage calc").Range("e4,f4").Copy Destination:=Sheets("sq footage calc").Range("e3,f3")
ActiveCell.Offset(2, -2).Copy Destination:=Sheets("sq footage calc").Range("B3").Value
ActiveCell.Offset(0, 2).Copy Destination:=Sheets("sq footage calc").Range("B3").Value
ActiveCell.Offset(-2, 3).Select

Loop

Thanks,
Mike

 

[pedie]

So you want to copy data excluding blank in B col? Just Col B and D or col C to D?

How does your data looks like...does it start from Row 1?


one code you can use without looping...

try this out;

[/FONT]
[FONT=Courier New]Option Explicit[/FONT]
[FONT=Courier New]Sub TEST1()[/FONT]
[FONT=Courier New]On Error GoTo errhandler[/FONT]
[FONT=Courier New]Dim lr As Long[/FONT]
[FONT=Courier New]lr = Sheets("drawer cutlist").Range("B" & Rows.Count).End(xlUp).Row[/FONT]
[FONT=Courier New]Sheets("drawer cutlist").Activate[/FONT]
[FONT=Courier New]ActiveSheet.AutoFilterMode = False[/FONT]
[FONT=Courier New]Range("B1:D" & lr).AutoFilter Field:=1, Criteria1:="<>"[/FONT]
[FONT=Courier New]Range("B1:D" & lr).SpecialCells(xlCellTypeVisible).Copy Sheets("sq footage calc").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)[/FONT]
[FONT=Courier New]Sheets("drawer cutlist").AutoFilterMode = False[/FONT]
 
[FONT=Courier New]Exit Sub[/FONT]
 
[FONT=Courier New]errhandler:[/FONT]
 
[FONT=Courier New]MsgBox Err.Number & ";" & Err.Description, vbCritical, "Unexpeeted Err"[/FONT]
 
[FONT=Courier New]End Sub[/FONT]

 

[Norie]

Remove Value from the destination.

 

[pedie]

What Norie is saying to do it this way...

[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]Worksheets("drawer cutlist").Range("B6").Select[/FONT]
[FONT=Courier New]Do Until ActiveCell.Value = ""[/FONT]
[FONT=Courier New]Sheets("Sq footage calc").Select[/FONT]
[FONT=Courier New]Rows("3:3").EntireRow.Select[/FONT]
[FONT=Courier New]Selection.Insert shift:=xlDown, copyorigin:=xlFormatFromLeftOrAbove[/FONT]
[FONT=Courier New]Range("a3").Select[/FONT]
[FONT=Courier New]ActiveCell.FormulaR1C1 = "2"[/FONT]
[FONT=Courier New]Range("E4:F4").Copy Destination:=Range("E3:F3")[/FONT]
[FONT=Courier New]Sheets("Drawer cutlist").Select[/FONT]
[FONT=Courier New]ActiveCell.Select[/FONT]
[FONT=Courier New]Selection.Copy[/FONT]
[FONT=Courier New]Sheets("Sq footage calc").Select[/FONT]
[FONT=Courier New]Range("b3").Select[/FONT]
[FONT=Courier New]Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/FONT]
[FONT=Courier New]:=False, Transpose:=False[/FONT]
[FONT=Courier New]Sheets("Drawer cutlist").Select[/FONT]
[FONT=Courier New]ActiveCell.Offset(0, 2).Range("A1").Select[/FONT]
[FONT=Courier New]Selection.Copy[/FONT]
[FONT=Courier New]Sheets("Sq footage calc").Select[/FONT]
[FONT=Courier New]Range("c3").Select[/FONT]
[FONT=Courier New]Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/FONT]
[FONT=Courier New]:=False, Transpose:=False[/FONT]
[FONT=Courier New]Rows("3:3").EntireRow.Select[/FONT]
[FONT=Courier New]Selection.Insert shift:=xlDown, copyorigin:=xlFormatFromLeftOrAbove[/FONT]
[FONT=Courier New]Range("a3").Select[/FONT]
[FONT=Courier New]ActiveCell.FormulaR1C1 = "2"[/FONT]
[FONT=Courier New]Range("E4:F4").Copy Destination:=Range("E3:F4")[/FONT]
[FONT=Courier New]Sheets("Drawer cutlist").Select[/FONT]
[FONT=Courier New]ActiveCell.Offset(2, -2).Copy Destination:=Sheets("sq footage calc").Range("b3")[/FONT]
[FONT=Courier New]ActiveCell.Offset(0, 2).Copy Destination:=Sheets("sq footage calc").Range("c3")[/FONT]
[FONT=Courier New]Worksheets("sq footage calc").Rows("3:3").EntireRow.Insert shift:=xlDown, copyorigin:=xlFormatFromLeftOrAbove[/FONT]
[FONT=Courier New]Worksheets("sq footage calc").Range("a3").FormulaR1C1 = "1"[/FONT]
[FONT=Courier New]Worksheets("sq footage calc").Range("e4,f4").Copy Destination:=Sheets("sq footage calc").Range("e3,f3")[/FONT]
[FONT=Courier New]ActiveCell.Offset(2, -2).Copy Destination:=Sheets("sq footage calc").Range("B3")[/FONT]
[FONT=Courier New]ActiveCell.Offset(0, 2).Copy Destination:=Sheets("sq footage calc").Range("B3")[/FONT]
[FONT=Courier New]ActiveCell.Offset(-2, 3).Select[/FONT]
[FONT=Courier New]Loop[/FONT]
[FONT=Courier New]

 

[Hayt]

That is a really cool macro and I can definitely think of a few ways I will use that in the future, but its not exactly what I wanted. The data on "drawer cutlist" is a little funky because it is set up so that the cabinet makers can take a quick look at it and know what pieces they need to cut out. I want to take the same data and put it into my sheet that calculates square footage so i can get a rough idea of how many sheets of plywood they are using. I want to copy the values from column b in "drawer cutlist" to column b in "sq footage calc" and column d in "drawer cutlist" to column c in "sq footage calc". on "drawer cutlist" my data starts on row 6, and on "sq footage calc" my data starts on row 3.

in "sq footage calc" my data is just in two rows, and in "drawer cutlist it looks like this:

...A...B C D
6......# x #
7
8......# x #

Thank you,
Mike

 

[Hayt]

that worked, thank you so much. I appreciate all the time you put into this, you really helped me out

 

[Hayt]

How do I just paste the values? I have formulas and formatting that I don't want to paste.

 

[Hayt]

never mind, i got it.