Excel VBA

Solved/Closed
ChrisBelfast Posts 10 Registration date Tuesday April 27, 2010 Status Member Last seen September 7, 2010 - Jun 29, 2010 at 05:38 PM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jul 6, 2010 at 06:08 PM
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

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jun 29, 2010 at 05:44 PM
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?
0
ChrisBelfast Posts 10 Registration date Tuesday April 27, 2010 Status Member Last seen September 7, 2010
Jun 29, 2010 at 05:54 PM
run-time error 1004, application defined or object defined error
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jun 30, 2010 at 03:05 AM
and what line is highlighted ? Do you have two sheets titles Sheet2 and Sheet1 ?
0
ChrisBelfast Posts 10 Registration date Tuesday April 27, 2010 Status Member Last seen September 7, 2010
Jun 30, 2010 at 08:53 AM
It's the 5th line, cells(rows.count, "a")~
0

Didn't find the answer you are looking for?

Ask a question
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jun 30, 2010 at 12:38 PM
Did you try this ?
Cells(Rows.Count, "A").End(xlUp).Offset(1,0).Select
0
ChrisBelfast Posts 10 Registration date Tuesday April 27, 2010 Status Member Last seen September 7, 2010
Jun 30, 2010 at 12:52 PM
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
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jun 30, 2010 at 06:18 PM
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
0
ChrisBelfast Posts 10 Registration date Tuesday April 27, 2010 Status Member Last seen September 7, 2010
Jul 1, 2010 at 04:49 AM
I'm logged on using my iPhone and my work desktop does have Internet access I'm afraid
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jul 1, 2010 at 04:53 AM
give this a shot

Cells(Rows.Count, 1).End(xlUp).Offset(1,0).Select
0
ChrisBelfast Posts 10 Registration date Tuesday April 27, 2010 Status Member Last seen September 7, 2010
Jul 5, 2010 at 02:04 PM
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
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jul 5, 2010 at 02:14 PM
Could that cell be locked?
0
ChrisBelfast Posts 10 Registration date Tuesday April 27, 2010 Status Member Last seen September 7, 2010
Jul 5, 2010 at 03:07 PM
its not currently locked
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jul 6, 2010 at 06:08 PM
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
0