Posting to next empty column

[Doddrell]

Hi
I have set up a macro to copy cell C3 from the 'Exports' tab to the next available cell in row F on the 'Revenue' tab starting in column 7. When I use it for the first and second times I get 'Run-time error 1004' however once there are at least two entries in the the receiving sheet (F7 and F8) it will work correctly. I'd really appreciate any help - I've been struggling with this for over a week.

Many thanks
Simon

Sub Import()
' Copies Period Number to next available column on Revenue sheet
Worksheets("Exports").Activate
ActiveSheet.Range("C3").Copy
Worksheets("Revenue").Activate
Range("F7").End(xlToRight).Offset(, 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

 

[Fluff]

Hi & welcome to MrExcel
Try

Cells(7, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial xlPasteValues

 

[My Aswer Is This]

That is because to start with you have nothing in Row 7 after column F to begin with. On sheet named Revenue.

And your script seems to be wanting to always copy the same Range("C3")

Why would you want to do that?
Please explain in detail what your wanting to do.

 

[Doddrell]

Fluff
Thanks for that - I've tried it and it seems to start posting in column B on 'Revenue' - is there a way of making it start in column F.
I really appreciate the help
Simon

 

[Doddrell]

Hi
Thanks for taking the time to help. Sorry if I didn't explain sufficiently. I'm writing a daily analysis spreadsheet to import data from three files I download every day from different sources. I use on macro to import the data, the second to manipulate it as required and the third to export it. The extract I uploaded worked perfectly as I wrote it in the middle of a month. Then when I cleared the sheet to start a new month it wouldn't work. I manually entered data for a few days and the macro started working again. I spent a week trying to rewrite it to no avail. I then stripped out that section to test separately to make sure it wan't conflicting with something else and it still didn't work which is when I posted it on the form. C3 on 'Exports' is just one cell in a much larger imported spreadsheet. F7 onwards on 'Revenue' is just one row on one of the output tabs. Sorry if I was ambiguous. THanks for any help.

Regards
Simon

 

[Fluff]

How about

Sub Import()
   Dim Lc As Long
   ' Copies Period Number to next available column on Revenue sheet
   Worksheets("Exports").Activate
   ActiveSheet.Range("C3").Copy
   Worksheets("Revenue").Activate
   Lc = Range("F7").End(xlToRight).Offset(, 1).Column
   If Lc < 6 Then Lc = 6
   Cells(7, Lc).PasteSpecial xlPasteValues
   Application.CutCopyMode = False
End Sub

 

[Doddrell]

Fluff
Thanks again - but that gives me Run Time Error 1004 on the row starting 'Lc = Range'
Regards
Simon

 

[Fluff]

Oops used your original code, rather than the modified version try

Lc=Cells(7, Columns.Count).End(xlToLeft).Offset(, 1).Column

 

[Doddrell]

Fluff
YEAAAHHH - works like a dream - thank you so much - I've spent a week tearing my hair out.
All your help really appreciated
Simon

 

[Fluff]

Glad to help & thanks for the feedback