Trying to Copy Multiple Columns from one workbook to another one

[Whiskerbizcuit]

Hey Everyone!

So I'm working on a project to help me out at work. I'm fairly new to VBA so sorry if I sound dumb :P I'm making a macro to copy some raw data from WB1.xlsx to WB2.xlsm The code below is what I'm working with so far, and it works fine, but I need the ranges of the columns to be variable because the data changes from week to week and The columns are never the same length. Any help you can give me would be greatly appreciated. Thanks For Listening!

Sub CopyPasteData()
Dim wb As Workbook
Application.CutCopyMode = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False

filePath = "d:\xxxx\xxx\xxxx\xxx.xlsx"
Set wb = Application.Workbooks.Open(filePath)

wb.ActiveSheet.Range("A2:A208").Copy Destination:=ThisWorkbook.Worksheets(2).Columns.Range("A4")

wb.ActiveSheet.Range("B2:B208").Copy Destination:=ThisWorkbook.Worksheets(2).Columns.Range("B4")

wb.ActiveSheet.Range("C2:C208").Copy Destination:=ThisWorkbook.Worksheets(2).Columns.Range("C4")

wb.ActiveSheet.Range("D2:D208").Copy Destination:=ThisWorkbook.Worksheets(2).Columns.Range("E4")

wb.ActiveSheet.Range("E2:E208").Copy Destination:=ThisWorkbook.Worksheets(2).Columns.Range("F4")
wb.Close False
Set wb = Nothing

Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.CutCopyMode = False

End Sub

 

[Whiskerbizcuit]

Thanks Fluff, unfortunately I get the same error in the same place. The Code I have Works, but only in the set range that is displayed (ie A2: A208).

 

[Whiskerbizcuit]

Is there any way to use the code I have and just set the range to grab the columns I specify to the end of the data in each column as it will vary + or - 100rows week to week?

 

[Whiskerbizcuit]

This is Awesome by the way, you guys are like Puzzle masters!!! Just finding the right pieces is tricky!

 

[Fluff]

Is there any way to use the code I have and just set the range to grab the columns I specify to the end of the data in each column as it will vary + or - 100rows week to week?

That's what my code should be doing.
Try this, it will come up with a message box showing a number, which should be the number of used rows in the data you're copying

Sub CopyPasteData()

    Dim wb As Workbook
    Dim UsdRws As Long
    Dim FilePth As String
    
Application.CutCopyMode = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False


    FilePth = "path"
    Set wb = Application.Workbooks.Open(FilePth)
    UsdRws = Cells.Find("*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    MsgBox UsdRws
'    wb.ActiveSheet.Range("A2:C" & UsdRws).Copy Destination:=ThisWorkbook.Worksheets(2).Range("A4")
'    wb.ActiveSheet.Range("D2:E" & UsdRws).Copy Destination:=ThisWorkbook.Worksheets(2).Range("E4")
    wb.Close False
    Set wb = Nothing

Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.CutCopyMode = False

End Sub

 

[Whiskerbizcuit]

OMG!!! Fluff, I tried to kiss you but my face keeps hitting the screen!! IT WORKED!!!! You Are Amazing!! Like a digital Samurai!!!

Thank you thank you thank you!!!!!!

 

[Whiskerbizcuit]

What is it like being right all the time!??!?! Quick, what are the winning lotto numbers!!! Let me guess, you have a macro for that?? hahaha THANKS AGAIN!!!!

 

[Fluff]

What is it like being right all the time!??!?!

Wouldn't know, never been there

Glad to help & thanks for the feedback