CODEWITHSUNDEEP
vbaexcel
Josh M.
Josh M.

Reputation: 27

VBA - Copy sheetvalues, paste to sheet with a variable name

I have a user form that allows people to input the date range of the series of data, which will then be later used to name the outputs from the rest of the macro. I am trying to copy all the values on the first sheet WWOutput, where all the data is analyzed/calculated and paste to a newly created sheet with the name of the variable DateRange, which will obviously change each time.

I am currently trying this: 

Worksheets.Add(Before:=Worksheets("Calc")).Name = DateRange.Value
Worksheets("WWOutput").Range("A1:Q1000000").Copy
Worksheets(DateRange).Range("A1").PasteSpecial Paste:=xlPasteValues

The new sheet is created and the worksheet is copied but it is not copying to the one I want. If I try .Cells instead of .Range it gives me "Type Mismatch"

What am I doing wrong?

Josh

Upvotes: 0

Views: 1797

Answers (2)

Dave
Dave

Reputation: 4356

Set ws = Worksheets.Add(Before:=Worksheets("Calc"))
ws.Name = DateRange.Value
Worksheets("WWOutput").Range("A1:Q1000000").Copy
ws.Range("A1").PasteSpecial Paste:=xlPasteValues

This should work for your needs. I'm setting an object reference to the worksheet you added (called ws) and then setting its name to the DateRange.Value. At that point it's simple enough to specify the worksheet necessary when pasting. This way you can make use of the reference in any number of ways while working with it. Let me know if you need any further explanation?

Upvotes: 1

user6432984
user6432984

Reputation:

You are missing .Value

Worksheets(DateRange.Value).Range("A1").PasteSpecial Paste:=xlPasteValues

Upvotes: 1

Related Questions