Compile error copying cells - VB

[Rocky0201]

Hi,

I'm getting a compile error when trying to copy cells in column G from row 4 to end to, column H from row 5 to end.

I set HBParmTC1 to "H5", HBParmTC2 to "H", ParmTC1 to "G4", ParmTC2 to "G"

Any help would be great...

Sheets("Parms").Range(HBParmTC1, .Range(HBParmTC2 & Rows.Count).End(xlUp)) _
.Copy Destination:=ThisWorkbook.Sheets("Parms").Range(ParmTC1, .Range(ParmTC2 & Rows.Count).End(xlUp))

Thanks

 

[Norie]

Are all those variables strings?

 

[Rocky0201]

Hi Nori,

Yes, they are strings.

Public ParmTC1 As String
Public ParmTC2 As String
Public HBParmTC1 As String
Public HBParmTC2 As String

After my first post, I realized that I had the variables reversed. HBParm variables needed to be my destination. I made the change and still recieved the compile error.

Sheets("Parms").Range(ParmTC1, .Range(ParmTC2 & Rows.Count).End(xlUp)) _
.Copy Destination:=ThisWorkbook.Sheets("Parms").Range(HBParmTC1, .Range(HBParmTC2 & Rows.Count).End(xlUp))

Thanks so much, Nori, for your help

 

[Rocky0201]

Hi Nori,

I'm not sure my 2nd Post regsitered. I should she a total of 3 posts but only shows 2, my original and your response. My laptop froze when I was paosting my reply to your posts. Making sure that you get this... Thanks

 

[Norie]

I don't think it's anything to do with the order.

Is there any other code, in particular a With <worksheet> type statement?

If you don't then something like this won't work.

.Range(ParmTC2 & Rows.Count).End(xlUp)

If you do it still might not work.

Which worksheets are you copying to/from or is the whole thing being done with the same worksheet?

If it's the same worksheet you could try something like this.

Set wsSrc = Worksheets("Parms")
 
LastRowSrc = wsSrc.Range("G"& Rows.Count).End(xlUp).Row
 
Set rngSrc = wsSrc.Range("G5:G" & LastRowSrc)
 
Set wsDst = Worksheets("Parms")
 
Set rngDst = wsDst.Range("H4")
 
rngSrc.Copy rngDst

I know that code probably isn't quite right, it's more of an example of another approach you could use..

It's a bit more code but, to me anyway, it's easier to follow and therefore easier to spot errors or alter.

PS You could still use your variables in that code, I left them out because I wasn't 100% sure if I was quite getting them.

 

[Rocky0201]

Thanks so much Nori...

I'm having a difficult time selecting cells not sequential in a column for deletion. As soon as I get this resolved, I'll give the copy routine a shot.

Thanks again...

 

[Rocky0201]

Norie,

Works like a charm... Thanks a bunch!!