Excel VBA

[Solved/Closed]
Report
Posts
10
Registration date
Tuesday April 27, 2010
Status
Member
Last seen
September 7, 2010
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

currently i have 2 worksheets one which users would use to complete a template and the other to store the information in.

i am trying to copy the information from a range (B2:B8) with in the 1st worksheet into a range (A2:H2) the code ive pieced together is:

Sheets("Sheet1").Select
Range("B2:B8").Select
Selection.Copy
Sheets("Sheet2").Select
Cells(Rows.Count, "A").End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

however the above code isnt working any ideas?

thanks in advance

13 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
I think

Cells(Rows.Count, "A").End(xlUp).Offset(1).Select

should be
Cells(Rows.Count, "A").End(xlUp).Offset(1,0).Select


though your code did work for me. What is the error?
Posts
10
Registration date
Tuesday April 27, 2010
Status
Member
Last seen
September 7, 2010

run-time error 1004, application defined or object defined error
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
and what line is highlighted ? Do you have two sheets titles Sheet2 and Sheet1 ?
Posts
10
Registration date
Tuesday April 27, 2010
Status
Member
Last seen
September 7, 2010

It's the 5th line, cells(rows.count, "a")~
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Did you try this ?
Cells(Rows.Count, "A").End(xlUp).Offset(1,0).Select
Posts
10
Registration date
Tuesday April 27, 2010
Status
Member
Last seen
September 7, 2010

yep same story and was highlighting the same line

the tab names are different sheet1 would be called CI template nad sheet2 would be called CI datatbase but the names in the code and tabs match
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Could you post a sample workbook with macro. You code worked for me. So there might be some thing related to sheet and data

Could you please upload a sample file with sample data etc on some shared site like https://authentification.site , http://wikisend.com/ ,https://accounts.google.com/ServiceLogin?passive=1209600&continue=https://docs.google.com/&followup=https://docs.google.com/&emr=1 http://www.editgrid.com etc and post back here the link
Posts
10
Registration date
Tuesday April 27, 2010
Status
Member
Last seen
September 7, 2010

I'm logged on using my iPhone and my work desktop does have Internet access I'm afraid
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
give this a shot

Cells(Rows.Count, 1).End(xlUp).Offset(1,0).Select
Posts
10
Registration date
Tuesday April 27, 2010
Status
Member
Last seen
September 7, 2010

nope still not got it working

doing a work around instead.

Range("B2:B8").Select
Selection.Copy
Range("A12").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Selection.Cut
Sheets("Sheet2").Select
Selection.Insert Shift:=xlDown

however while the above is working i need to leave the A2 cell selected in Sheet2, i was trying the below but it wouldnt work the "Range("A2").Select" was the line thats causing the issue, any ideas here?

Range("B2:B8").Select
Selection.Copy
Range("A12").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Selection.Cut
Sheets("Sheet2").Select
Range("A2").Select
Selection.Insert Shift:=xlDown
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Could that cell be locked?
Posts
10
Registration date
Tuesday April 27, 2010
Status
Member
Last seen
September 7, 2010

its not currently locked
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Well hard to tell without having a look

Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too