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

 

[rossross]

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

Yes! This almost hits the nail on the head only issue is it went down 2 too many rows. Do I add offset(-2) behind the last row? adding it in the last lr = bit gives me an object error.

 

[Joe4]

Just subtract 2 from the lr calculation, i.e.

lr = wstemplate.Cells(Rows.Count, "B").End(xlUp).Row - 2

 

[rossross]

Just subtract 2 from the lr calculation, i.e.

lr = wstemplate.Cells(Rows.Count, "B").End(xlUp).Row - 2

Okay I just realized it actually dropped the formats down to row 31,961 which is way past where I needed it to be. Should've stopped at 1956 in this instance. But we're close, and I appreciate this!

 

[Joe4]

See if this fixes it:

lr = wstemplate.Cells(wstemplate.Rows.Count, "B").End(xlUp).Row - 2

If not, is there something in column down by row 31,961 or 31,963?
Are you pre-formatting or something all the way down?

Could maybe also try going from the top down, i.e.

lr = wstemplate.Range("B2").End(xlDown).Row

 

[rossross]

See if this fixes it:

lr = wstemplate.Cells(wstemplate.Rows.Count, "B").End(xlUp).Row[B] - 2[/B]

If not, is there something in column down by row 31,961 or 31,963?
Are you pre-formatting or something all the way down?

Could maybe also try going from the top down, i.e.

lr = wstemplate.Range("B2").End(xlDown).Row

That second one results in same issue and for some reason it's not letting me insert so those brackets [ ] or i'm just doing it wrong. again, i'm brand new to this. And no, no data down there. ctrl + end puts me right at the end of my data where it should be.

 

[Joe4]

That second one results in same issue and for some reason it's not letting me insert so those brackets [ ] or i'm just doing it wrong. again, i'm brand new to this. And no, no data down there. ctrl + end puts me right at the end of my data where it should be.

Sorry, those "B"s were not supposed to be there.
I tried adding bold formatting to my VBA code, and you cannot do that.
I went back and updated the reply.
Can you try that one again?

 

[rossross]

Son of a gun.. I changed the -2 to -7 and then i realized you had the paste range as "B3: CE" instead of CE3" and i changed that and now it works!

 

[Joe4]

Son of a gun.. I changed the -2 to -7 and then i realized you had the paste range as "B3: CE" instead of CE3" and i changed that and now it works!

Well, that solves that mystery! Yes, since we are calculating the ending row number, we want to totally remove what was there before.
This is why I usually recommend using Copy/Paste, so we can be sure that we are comparing apples-to-apples, and remove typos from the possibility of differences in what we are talking about.

 

[rossross]

Well, that solves that mystery! Yes, since we are calculating the ending row number, we want to totally remove what was there before.
This is why I usually recommend using Copy/Paste, so we can be sure that we are comparing apples-to-apples, and remove typos from the possibility of differences in what we are talking about.

I wanted to get the hang on actually writing it, but yes, that would've been the better practice. I did the same for the formulas, and that works as well. You have no idea how much of a help you have been.

 

[Joe4]

Excellent!
I am just glad that we got it all sorted out.