Copy and Paste Macro

[dgrowe]

I'd like to copy and paste a range of data using a macro button from one worksheet to another. The data will be variable so I'll need to identify how many rows exist to establish what to copy. I know this is probably fairly easy but I'm a novice with VB code. Thanks.

 

[Smitty]

If you record a macro performing what you want, then post it back here, someone can set it up dynamically for you.

That way we'll have the sheet names and an idea of the range that's being copied. Just note which column will most consistenly have data in it, because that's how you'll gauge the range size.

 

[dgrowe]

This particular data set had 73 lines.


Sub Macro1()
'
' Macro1 Macro
'

'
Sheets("BUD FG").Select
Range("G4:G76").Select
Selection.Copy
Sheets("Sheet4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

 

[Smitty]

Here's one way to do it:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> Macro1()<br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> ws = Sheets("BUD FG")<br>    lr = ws.Cells(Rows.Count, "G").End(xlUp).Row<br>    <br>    ws.Range("G4:G" & lr).Copy<br>    Sheets("Sheet4").Range("A1").PasteSpecial Paste:=xlPasteValues<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

You didn't say what the destination range was, so I just put it in A1. Just adjust it as needed.

HTH,

 

[dgrowe]

Worked perfectly! Thanks.

 

[dgrowe]

Is there a way to add a 2nd column to the macro? I'd like to add column D in addition to G.

 

[Smitty]

I'd just add that as a separate condition.

ws.Range("D4:D" & lr).Copy
Paste here

Noting that will use the last cell in G as the ending range for D, so if you need to find the last row in D you'll need to repeat the procedure.

Can you use D:G, or do the columns need to be separate? What should the destinations be?

 

[dgrowe]

Ok, I'll give it a try. The columns need to be separate because I don't want to paste the data in between D and G. The destination will be Sheet4 again in cell A1.

 

[Smitty]

The destination will be Sheet4 again in cell A1.

Ummm, won't that just wipe out whatever you paste first? Not counting any potential overlap/gap between D & G's last rows.

 

[dgrowe]

Yeah. Sorry, meant A2.