Format copying not working

[rossross]

This is my full code

Sub StripData()

Call InsertData
Call FormatData
'Call DragData

End Sub

Sub InsertData()

Dim myWb As Workbook
Dim myRowsToCopy As Range

Set wbtemplate = Workbooks("Template.xlsm").Worksheets("Raw")
Set formatrange = Range("B2:CE2")
Set formularange = Range("AK2:CE2")
Set myWb = Workbooks.Open("C:\Users\me\Desktop\Practice\Data_Pull_Dummy.xlsx")

With myWb.Sheets("Data").Range("A:XFD")
    Set myRowsToCopy = Range(.Cells(.Rows.Count, 1).End(xlUp), .Cells(2, .Columns.Count))
End With

myRowsToCopy.Copy
wbtemplate.Range("3:3").Insert Shift:=xlDown
Application.CutCopyMode = False


End Sub

Sub FormatData()

Dim wbtemplate As Workbook
Set template = Workbooks("Template.xlsm").Worksheets("Raw")
template.Range("B2:CE2").Copy Destination:=Sheets("Raw").Range("B" & Rows.Count).End(xlUp).Offset(-7)

End Sub

Essentially, I'm pulling in data and inserting it. Then I need to copy the format down from the top row to the bottom row. Cannot get it to work correctly.

Any help at all please

 

[rlv01]

Try using .PasteSpecial

Sub FormatData()
    
    Dim wbtemplate As Workbook
    Set wbtemplate = Workbooks("Template.xlsm").Worksheets("Raw")
    wbtemplate.Range("B2:CE2").Copy
    Sheets("Raw").Range("B" & Rows.Count).End(xlUp).Offset(-7).PasteSpecial Paste:=xlPasteFormats
    
End Sub

 

[rossross]

Try using .PasteSpecial

Sub FormatData()
   
    Dim wbtemplate As Workbook
    Set wbtemplate = Workbooks("Template.xlsm").Worksheets("Raw")
    wbtemplate.Range("B2:CE2").Copy
    Sheets("Raw").Range("B" & Rows.Count).End(xlUp).Offset(-7).PasteSpecial Paste:=xlPasteFormats
   
End Sub

"Subscript out of range" error occurs with this. but it did select the first row that i want copied.

 

[rlv01]

Likely this is returning a bad range.

Sheets("Raw").Range("B" & Rows.Count).End(xlUp).Offset(-7)

 

[rossross]

Likely this is returning a bad range.

Sheets("Raw").Range("B" & Rows.Count).End(xlUp).Offset(-7)

how would you advise me to fix this? I am new to this and clearly have no clue what i'm doing

 

[Joe4]

On your "Raw" sheet, what is the last row in column B with data in it?

 

[rossross]

On your "Raw" sheet, what is the last row in column B with data in it?

at start, its set up like this:

Row 1: headers
Row 2: placeholder with formulas
Row 3: placeholder with formulas
Row 4: blank
Row 5: weighted averages
Row 6: counts the minimum value or is blank (each column depends)
Row 7: counts the maximum value or is blank (each column depends)
Row 8: two cells that counts the average in in column B and C
Row 9: blank
Row 10: counts % NA

Row 10 is the last row at the start. But then I insert the data from the other workbook. that changes and shifts all the rows below 2 down to whatever it ends up being.

 

[Joe4]

at start, its set up like this:

Row 1: headers
Row 2: placeholder with formulas
Row 3: placeholder with formulas
Row 4: blank
Row 5: weighted averages
Row 6: counts the minimum value or is blank (each column depends)
Row 7: counts the maximum value or is blank (each column depends)
Row 8: two cells that counts the average in in column B and C
Row 9: blank
Row 10: counts % NA

Row 10 is the last row at the start. But then I insert the data from the other workbook. that changes and shifts all the rows below 2 down to whatever it ends up being.

Not quite what I was looking. I am looking for a literal row number, like if you did this instead:

MsgBox Sheets("Raw").Range("B" & Rows.Count).End(xlUp).Row

Basically, that line of code RLV01 highlighted is trying to move up 7 rows from the last row in column B with data.
So, if that row number is not 8 or more, that line would cause an error, as you cannot have row numbers less than 1.

 

[rossross]

Not quite what I was looking. I am looking for a literal row number, like if you did this instead:

MsgBox Sheets("Raw").Range("B" & Rows.Count).End(xlUp).Row

Basically, that line of code RLV01 highlighted is trying to move up 7 rows from the last row in column B with data.
So, if that row number is not 8 or more, that line would cause an error, as you cannot have row numbers less than 1.

To check to see if it was an order of operations error, I made it pop up to see how many rows of data were currently used after I inserted so I know it should operate in order. I got the msg box to pop up, it says (for this instance for this particular set): 1954. Which is Almost correct as the data goes to row 1956.

If I remove the -7 offset, all it does is insert my data, and then copy the format to the very last line which is what I have listed as row 10 above (but now row 1983). And I don't need it on that line. I need it to copy down every row from 2 to 1956.

 

[Joe4]

Maybe try something like this:

Sub FormatData()

Dim wstemplate As Worksheet
Dim lr As Long

Set wstemplate = Workbooks("Template.xlsm").Worksheets("Raw")
lr = wstemplate.Cells(Rows.Count, "B").End(xlUp).Row

wstemplate.Range("B2:CE2").Copy
wstemplate.Range("B3:CE" & lr).PasteSpecial Paste:=xlPasteFormats

End Sub