Excel VBASolved/Closed

Question

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

Configuration: Windows 7 / Firefox 3.6.3

rizvisa1 · Answer

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?

ChrisBelfast · Answer

run-time error 1004, application defined or object defined error

rizvisa1 · Answer

and what line is highlighted ? Do you have two sheets titles Sheet2 and Sheet1 ?

ChrisBelfast · Answer

It's the 5th line, cells(rows.count, "a")~

rizvisa1 · Answer

Did you try this ?
Cells(Rows.Count, "A").End(xlUp).Offset(1,0).Select

ChrisBelfast · Answer

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

rizvisa1 · Answer

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

ChrisBelfast · Answer

I'm logged on using my iPhone and my work desktop does have Internet access I'm afraid

rizvisa1 · Answer

give this a shot 

Cells(Rows.Count, 1).End(xlUp).Offset(1,0).Select

ChrisBelfast · Answer

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

rizvisa1 · Answer

Could that cell be locked?

ChrisBelfast · Answer

its not currently locked

rizvisa1 · Answer

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

Excel VBA

13 responses

Similar discussions

Subscribe To Our Newsletter!