Excel: VBA Code to Find Last Cell To Copy and Paste 3

[hahnsm]

I am in need of code that will allow me to Select data on one sheet in Excel, Copy that data, and then Paste it to another sheet in the same workbook but at the End of the current information on that (Paste to) sheet.
What code can I use to:
Select my data on one sheet
Copy it
Select a different sheet
Find the last cell of informtion and offset to go to the next row and first column to Ex: ActiveCell.Offset (1, [-2])
Paste the copied data

Please Help!!
Thanks

 

[Bowers74]

Try:

Sheet1.[A1].Copy Destination:=Sheet2.[A65536].end(xlUp).Offset(1,0)

Confused about the code:

Read my FAQ: faq707-4090 Refer to Worksheets more effecively in a Procedure

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[hahnsm]

Mike:
The code didn't do anything for me. I am putting the code directed with the sheet (Sheet1)that the command button is located. On the button click, I want the copy and paste to occur. From Sheet1 on the button click, I want the code to go to Sheet5, Copy all info then go to Sheet12, find the next blank cell / row, and paste the copied information.

Please help!!
Thank you

Sadie

 

[VBAjedi]

Mike's approach should work. Just plug in the correct sheet names and ranges, and put the code in your button's click event.

If the number of rows in your source data is going to be changing, modify the code as follows (note: change the references to the top left cell and number of columns your source data uses to match your data):

LRow = Sheet5.[A65536].end(xlUp).row
Sheet5.Range("A1:Z" & LRow).Copy Destination:=Sheet12.[A65536].end(xlUp).Offset(1,0)



VBAjedi

 

[hahnsm]

Alright, I have a new problem here. I do not want it to paste 'All' just the 'Value'. I tried:

Private Sub CommandButton1_Click()
Dim i As Integer

For i = 3 To 11
Sheets("i&quot.Select
Range("A1:B35&quot.Select
Selection.Copy
Sheets("Report&quot.[A65536].End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Next i

I get a runtime error '9' Subcript out of Range on Sheets("i&quot.Select.
I do not know what that error nor how to fix it!
Can anyone help??

 

[DaleWatson123321]

hahnsm,

Give this a try. It worked for me.

For i = 3 To 11
Sheets(i).Select
Range("A1:B35&quot.Select
Selection.Copy
Worksheets("Report&quot.Select
NextCell = [A65536].End(xlUp).Offset(1, 0).Address
Range(NextCell).Select
Selection.PasteSpecial Paste:=xlValues
Next i

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[hahnsm]

Dale:
Thank you!! That code was what I needed!!

THANKS
SADIE

 

[Luis939]

For i = 3 To 11
Sheets("Report&quot.Range("A1:B35&quot.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Next i

this is much smaller code, combining those extra steps, the
reason u got a runtime error '9' Subcript out of Range on Sheets("i&quot.Select is becuase u cant put " " around the i, becuase 'i' is not a literal value to be taken in other words there is not such worksheet named "i" in your workbook CAUTION: if there was a worksheet with that name then it would paste all that data in that sheet, so when u exclude the quotes the value in variable 'i' is used as the worksheet in this case sheet(3) means the third sheet or sheet # 3....hope that helps clear a few more things up!