Copying cells from Sheet1 to Sheet2 based on count of cells with data on Sheet2

[JDescant]

Please forgive my ignorance, but I'm new to VBA and I've exhausted search efforts for an answer

I'm trying to "export" data from Sheet1 to Sheet2 based on the count of cells from Sheet2. Paste will be in next available row, in the columns right next to existing data. Below is my code, but I'm really just referencing the second half here since the first half works. I'm getting an error on this code, and I'm sure it's because I just made something up from all the random research I did trying to build an applicable code.

The end goal is to take data from the first set of code, copy / paste-transpose into new sheet. Then take the data from adjacent cells and copy / paste them to match with the number of cells transposed in first step.

I hope I'm making sense.


Sub Row_10()
'
' Row_10 Macro
'

'Copy and transpose treatment data
Range("G9:N9,G11:N11").Select
Range("G11").Activate
Selection.Copy
Sheets("Billing Form").Select
Range("D" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Application.CutCopyMode = False
' Then copy Location and Wellmaster based on count of cells
Sheets("Wednesday Filmplus").Range("D11:E11", Sheets("Billing Form").Range("D" & Rows.Count).End(xlUp)).Copy
Sheets("Billing Form").Select
Range("B" & Rows.Count).End(xlUp).Offset(1).Select
Selection.Paste
Application.CutCopyMode = False

 

[JLGWhiz]

Selection.Paste

This is probably throwing the error. Should be

ActiveSheet.Paste

when using the 'Select and Activate coding method.

 

[JLGWhiz]

duplicate deleted/

 

[JLGWhiz]

Somehow the edit of my post got fouled up.

Sheets("Wednesday Filmplus").Range("D11:E11", Sheets("Billing Form").Range("D" & Rows.Count).End(xlUp)).Copy

This is an invalid statement, because it tries to define a range across two worksheets and that won't work. What did you want that statement to do?

Maybe post a screen shot of a before and after to show what you are trying to accomplish.

 

[JDescant]

I continued to work on this after posting and came up with the following simplified solution. Seems to work other than the FillDown statement - I need it to grab the data that was just pasted. I will be "duplicating" this same task for each subsequent row in the original sheet. Don't exactly want to fill down value in B9:C9 every time, that's just where the data starts. Screenshots also below

Sub Row_10()
'
'Row_10 Macro
'
'Copy and transpose treatment data
    Range("G9:N9,G10:N10").Select
    Range("G10").Activate
    Selection.Copy
    Sheets("Billing Form").Select
    Range("D" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
        Application.CutCopyMode = False
'Then copy and paste Location & Wellmaster based on # of transposed cells above
    Sheets("Wednesday Filmplus").Select
    Range("D10:E10").Select
    Selection.Copy
    Sheets("Billing Form").Select
    Range("B" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Dim LastRow As Long
    LastRow = Cells(Cells.Rows.Count, "D").End(xlUp).Row
    Range("B9:C" & LastRow).FillDown
End Sub

This is where I'm grabbing data from

And this is where data is going. Goal is to have a macro out to the side of each row to export its data and drop it into the new sheet without pasting over existing data.

 

[JLGWhiz]

[Quoote]Goal is to have a macro out to the side of each row to export its data and drop it into the new sheet without pasting over existing data[/Quote]

I don't know what this means. Macros go into the code modules, not on the sheets. Formulas go on sheets. Macros are short executable code procedures and can be in the form of a Sub or a user defined function. A group of macros may be organized into a call sequence to for a program that performs many fountions using multiple sources, hardware and code modules.

But using the macro recorder and getting advice from responders in forums is a good way to enhance code writing skills.

Instead of this [CODE=vba]Range("B9:C" & LastRow).FillDown

maybe something like this
If Range("B10") = "" Then
Range("B9:C" & LastRow).FillDown
Else
Range(Cells(Rows.Count, 2).End(xlUP), "C" & LastRow).FillDown
End If[/CODE]
Which wouild start at B9 and fill down if no entries are in B10, but if B10 has entries, it will then look for the last row with a value in column B and fill down from there to the the LastRow row.

 

[JDescant]

This is perfect. Again, I apologize for my ignorance. I'm still trying to understand and learn all the functions possible in VBA. Macro recorder is what it is and I'm not really learning from it.

 

[JLGWhiz]

This is perfect. Again, I apologize for my ignorance. I'm still trying to understand and learn all the functions possible in VBA. Macro recorder is what it is and I'm not really learning from it.

that is what the forum is for. Hone your skills.
Regards, JLG

 

[JDescant]

I think I backed myself into another corner on this task. I created another macro to "export" the data by using the run.application command - I have a macro for each specific row. However, when I don't have data in a row, the fill.down command still wants to execute. I know there's a way to incorporate an IFBlank statement. Below are my current macros

This macro is is duplicated for each specific row

Sub Row_11()
'
'Row_11 Macro
'
'Copy and transpose treatment data
    Range("F9:I9,F11:I11").Select
    Range("F11").Activate
    Selection.Copy
    Sheets("Billing Form").Select
    Range("D" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
        Application.CutCopyMode = False
'Then copy and paste Location & Wellmaster based on # of transposed cells above
    Sheets("GEP 1").Select
    Range("A11,C11:D11").Select
    Selection.Copy
    Sheets("Billing Form").Select
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Dim LastRow As Long
    LastRow = Cells(Cells.Rows.Count, "D").End(xlUp).Row
    If Range("A10") = "" Then
    Range("A9:C" & LastRow).FillDown
    Sheets("GEP 1").Select
    Else
    Range(Cells(Rows.Count, 1).End(xlUp), "C" & LastRow).FillDown
    Sheets("GEP 1").Select
    End If
    
End Sub

Then I use this to "export" the data.

Sub Export_GEP_1()
'
' Export Macro
'

'
    Application.Run "Module3.Row_10"
    Application.Run "Module3.Row_11"
    Application.Run "Module3.Row_12"
    Application.Run "Module3.Row_13"
    Application.Run "Module3.Row_14"
    Application.Run "Module3.Row_15"
    Application.Run "Module3.Row_16"
    Application.Run "Module3.Row_17"
    Application.Run "Module3.Row_18"
    Application.Run "Module3.Row_19"
    Application.Run "Module3.Row_20"
    Application.Run "Module3.Row_21"
    Application.Run "Module3.Row_22"
    Application.Run "Module3.Row_23"
    Application.Run "Module3.Row_24"
End Sub

Should I have just used a loop to begin with? I was trying to steer away from them because I don't understand the code and how it works.

 

[JLGWhiz]

Yoiu would need to start a new thread for that problem. It is unrelated to the original post in this thread.
Regards, JLG