copy paste from one workbook to another and find specific worksheet

[RedOctoberKnight]

Good Morning.

I have a simple copy paste code from workbook to another but my question is, how do I get it to find a specific sheet based on value in C1 and paste there?

Workbooks("Truck Schedule Builder.xlsm").Worksheets("SUN").Range("A2:D9").Copy _
    Workbooks("Truckschedulesduelsides.xlsm").Worksheets("Sun 4-3").Range("A2")

I want it to copy the Truck Schedule Builder worksheet and paste it into the truckschedulesduelsides workbook but find the specific sheet that has the same name as the value in C1 on original copy sheet (truck schedule builder)

Any help would be much appreciated.

Thanks,

 

[Fluff]

How about

With Workbooks("Truck Schedule Builder.xlsm").Worksheets("SUN")
   .Range("A2:D9").Copy Workbooks("Truckschedulesduelsides.xlsm").Worksheets(.Range("C1").Text).Range("A2")
End With

 

[RedOctoberKnight]

Thanks fluff but I get a compile error "invalid or unqualified reference"

 

[Fluff]

You shouldn't get that error, did you copy/paste what I suggested, or re-type it?

 

[RedOctoberKnight]

You shouldn't get that error, did you copy/paste what I suggested, or re-type it?

My apologies, I forgot to copy the end with over too. Works now. Thank you so much.

Can I have it paste values only?

 

[Fluff]

How about

With Workbooks("Truck Schedule Builder.xlsm").Worksheets("SUN")
   Workbooks("Truckschedulesduelsides.xlsm").Worksheets(.Range("C1").Text).Range("A2:D9").Value = .Range("A2:D9").Value
End With

 

[RedOctoberKnight]

How about

With Workbooks("Truck Schedule Builder.xlsm").Worksheets("SUN")
   Workbooks("Truckschedulesduelsides.xlsm").Worksheets(.Range("C1").Text).Range("A2:D9").Value = .Range("A2:D9").Value
End With

Thanks Fluff. As always very much appreciated.

 

[Fluff]

Glad to help & thanks for the feedback.

 

[RedOctoberKnight]

Glad to help & thanks for the feedback.

Hey Fluff, I was wondering if you can add something to the code below?

When it pastes values to truckschedulesduelsides.xlsm, How do I get it to delete blank cells in range B5:P39 and shift up?

With Workbooks("Truck Schedule Builder.xlsm").Worksheets("SUNDAY")
   Workbooks("Truckschedulesdualsides.xlsm").Worksheets(.Range("C1").Text).Range("G5:O39").Value = .Range("AA17:AI51").Value
   End With

I've tried adding the code below that I found on another thread you suggested but I cant get it to work.

With Range("B5:P39")
   .Value = .Value
   .SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
End With

Thanks,

 

[Fluff]

As this is a total different question, it needs a new thread. Thanks