0

I am trying to figure out to copy data from one workbook to another workbook. The issue I keep having is that the workbook I am copying from will change names every time.

The work book I am pasting the data to will stay the same. I have down how to grab the data I am just having the issue of switching back to the workbook I am copying the data from.

this is what I have so far.

Sub grabdata()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim w As Workbook

w = ActiveWorkbook

lrow = ThisWorkbook.Sheets("Month").Cells(Rows.Count, 3).End(xlUp).Row
LoopEnd = lrow - 16

LRowTxtTab = 2

For x = 32 To LoopEnd

' Grabs Metrics from Month tab (This is the grab data from workbook and will       change everytime

ThisWorkbook.Sheets("Month").Select
GridEnd = x + 16
Range("D" & x, "V" & GridEnd).Copy


(this is the workbook that will stay the same all the time)
Workbooks("NRBSrub").Activate
Workbooks("NRBSrub").Worksheets("Sheet5").Select

Range("E" & LRowTxtTab).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=True

' Grabs assocaited brands per metrics copied over above
BrandName = ThisWorkbook.Sheets("Month").Range("A" & x)
Sheets("Sheet5").Range("B" & LRowTxtTab, "B" & LRowTxtTab + 18) = BrandName

LRowTxtTab = LRowTxtTab + 19
x = x + 17

Next x

' Grabs FY time frames for FISC_MO column
LRowTxtTab = ThisWorkbook.Sheets("Sheet5").Cells(Rows.Count, 
5).End(xlUp).Row
ThisWorkbook.Sheets("Month").Range("D2:V2").Copy
Sheets("Sheet5").Range("C2").PasteSpecial Paste:=xlPasteValues,   
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True

Application.ScreenUpdating = True`enter code here`
Application.DisplayAlerts = True

End Sub
0m3r
  • 12,286
  • 15
  • 35
  • 71
user3562334
  • 31
  • 2
  • 5

1 Answers1

1

I am just having the issue of switching back to the workbook I am copying the data from

use Set 

Dim w As Workbook: Set w = ActiveWorkbook

then you can switch between workbooks like this:

w.Activate

or use with method

With w
    lrow = .Sheets("Month").Cells(Rows.Count, 3).End(xlUp).Row
    'do whatever you need with w
End with
With Workbooks("NRBSrub")
    'do whatever you need with Workbooks("NRBSrub")
End With
Vasily
  • 5,707
  • 3
  • 19
  • 34