Hii

[TVSHOWLISTER]

HI i am new here my name is Christy, I joined this site because i am having trouble with a particular VBA code

I want to take information from cells in work sheet2 and put it into work sheet1 last row which already has 1865 rows full of information

The Range from sheet1 is B1-B9, (which is a mostly a Static array so i use the copy feature here for the text entered)

I want to paste these cells into Sheet 1 B-J In the last row

I can provide pictures (i have 3) if you like to explain it better, i didnt really know the rules on pictures for noobs here lol

BTW Column A, uses an IF formula for ID field


EDIT:
BTW i want to use a Macro button for this

Its for listing/rating TV Shows im watching, i just want to make the process easier

 

[MARK858]

The Range from sheet1 is B1-B9

Are you sure that the above shouldn't read?

The Range from sheet2 is B1-B9

Also just to be clear do you mean paste in the last row with data or the first row after the last row with data (and what column are you basing the last row on?)?

 

[TVSHOWLISTER]

No The data form is in sheet1

i want to paste from the data form i manually created, from sheet1, to sheet2 first row after the last row with data

The data in sheet2 column range is B-J, i would like to transpose it from sheet1 data form


example
From Sheet1 Data form

B1
TV Show
B2
Helper (season)
B3
Episode no.
B4
Episode Name
B5
Year
B6
Release Date
B7
Rating
B8
Rating Helper
B9
Watched

to
Sheet2 (table last row after data entered)

A
IF formula that returns the ID for example my last this code is telling me that the last row of data entered is 1865 (i want to be able to automatically add data after this using a macro)

A
IF formula will bring up the next row entered id
B
TV Show
C
Helper (season)
D
Episode no.
E
Episode Name
F
Year
G
Release Date
H
Rating
I
Rating Helper
J
Watched

I tried recording Transpose onto the next row after data but something went wrong with it

Sorry for the lengthy post

 

[TVSHOWLISTER]

I have been manually copying data form from sheet1 to the first row after the last data entered, and manually transposing it into the table

 

[MARK858]

I want to paste these cells into Sheet 1 B-J In the last row

So the above was incorrect and it is Sheet2?

You didn't answer the question below

and what column are you basing the last row on?

I will assume that you are basing the last row on column B and you mean the first row after the last row with data in B.


Based on all the above

Sub CopyTrans()
    Application.ScreenUpdating = False
    Sheets("Sheet1").Range("B1:B9").Copy
    Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteAll, Transpose:=True
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
End Sub

 

[TVSHOWLISTER]

So the above was incorrect and it is Sheet2?

You didn't answer the question below


I will assume that you are basing the last row on column B and you mean the first row after the last row with data in B.


Based on all the above

Sub CopyTrans()
    Application.ScreenUpdating = False
    Sheets("Sheet1").Range("B1:B9").Copy
    Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteAll, Transpose:=True
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
End Sub

The B1:B9 range is from sheet 2
and that data is to be entered into sheet 1, my ID column (A) currently reads 1865 in my table (which the IF formula i use goes to the very bottom), i want to post the new data into 1866, and then the data i enter in B1:B9 after that i want to post into 18677, which the IF formula i have running in Column A will Return once data from B1:B9 range is entered in after the last ID counted which is currently 1865

I am using this formla in Sheet 1 Column A
=IF(B1866="","",SUBTOTAL(3,$B$2:B1866))

Sorry im not explaining it very well i can see it in my head what i want to do but i am confused how to code it properly

 

[TVSHOWLISTER]

I tried that code (thanks for providing it btw), and because of the IF formula used in Column (A) it returned the new data from B1:B9 at the very bottom of worksheet 1 LOL sorry again :s

 

[TVSHOWLISTER]

Excel seems to have corrected this issue itself and its working now thank you :D

 

[MARK858]

The B1:B9 range is from sheet 2

Please make up your mind in post number 3 you stated

paste from the data form i manually created, from sheet1, to sheet2

I am going out for a few hours in the meantime can you please answer the questions below so it is clear (please do not add any more detail than what the questions ask).


What sheet is the range B1:B9 being copied from?
What sheet is the data being pasted to?
What is in column B of the sheet being pasted to, is it raw data and blanks or are there formulas returning ""?


I will look at it when I return.

 

[TVSHOWLISTER]

This is actually my first time asking a Excel related question so im sorry if it is annoying you

B1:B9 is sheet2
the data is being posted to sheet 1 (column B)
i want new data entered in blanks (after already entered data) in column B sheet1

I am using an IF formula (provided) in column (A) returning number of rows already used in Column B, if blank, Column A doesnt count anything

The code you provided sort of does work, but it overrides the last row of data entered in sheet 1 i want it to be on the next blank cell