Solved VBA copy from one sheet to another with one cell blank

June 26, 2017 at 21:57:21
Specs: Windows 7
Does anyone has any idea of doing a code for

From sheet1

	Column a
row 1	zone1
row 2	zone2
row 3	zone3
row 4	zone4
row 5	zone5
row 6	zone6
row 7	zone7
row 8	zone8
row 9	zone9

to sheet2

	Column b
row 1	leave blank
row 2	zone1
row 3	zone2
row 4	zone3
row 5	zone4
row 6	zone5
row 7	zone6
row 8	zone7
row 9	zone8
row 10	zone9

message edited by suzysss

See More: VBA copy from one sheet to another with one cell blank

Report •

June 27, 2017 at 00:24:09
Something like this will work, assuming the sheet being copied from is called
and the sheet being copied to is called

Sub CopyPaste()
    Sheet1.Range("A1", Range("A" & Rows.Count).End(xlUp)).Copy Destination:=Sheet2.Range("B2")
End Sub

message edited by AlwaysWillingToLearn

Report •

June 27, 2017 at 01:36:49
you are so fast!

can I ask if you know why if I change the sheet name i.e. change sheet1 to raw, change sheet2 to detail, then the below code is not working?

Private Sub CommandButton1_Click()

raw.Range("A1", Range("A" & Rows.Count).End(xlUp)).Copy Destination:=detail.Range("B2")

End Sub

Report •

June 27, 2017 at 01:44:34
✔ Best Answer

This is because 'raw' is the name of a sheet and therefore you will need to address it accordingly, sheet1 in my example was not only the name of the sheet but also the sheet object, sheet 1 can be called anything I wanted but when I refer to the object I always refer to that sheet. So for your example to work, you will have to reference the sheet name, as below

Private Sub CommandButton1_Click()

Sheets("raw").Range("A1", Sheets("raw").Range("A" & Rows.Count).End(xlUp)).Copy Destination:=Sheets("detail").Range("B2")

End Su

Report •

Related Solutions

June 27, 2017 at 01:55:13
oh.. I see.. I get it.. your code works perfectly! thanks so much for your help!!

And I have 2 more questions.. if you can guide me..
1. Does it matter where I place the buttons? Will my code be the same if I put the button in sheet1 or sheet2?
2. What's the difference between Worksheets("") and Sheets("") are they doing the same thing?


Report •

June 27, 2017 at 02:02:13
You can in theory place the buttons wherever you like with one consideration.

With your code we are referencing a sheet directly so you can put the button on sheet 2 and it will work.

We are referencing the sheet directly


However, if the code was referencing Active sheet


Then you will have to place the code on the sheet on which your data is, otherwise the code will be working on the sheet on which the button is placed, as that is the Active Sheet.

Sheets and Worksheets are the same thing, I am just lazy so opt for use the shorter version :)

Report •

June 27, 2017 at 02:16:08
Brilliant! Thanks a lot ^^

Report •

Ask Question