0

I'm trying to figure out how to past a column of data into a sheet and then rerun the macro again so that I can select a different column to be posted in the sheet that was just created but I cant figure out how to write my IF statements correctly. This is what I have so far. The error tells me that the sheet already exists but I need it to be able to paste into that sheet if it is true and if the sheet doesn't then it needs to create it. I believe there is an error with either the for each loop or the first if statement.

Sub Macro_2424()

    Dim FromRange As range
    Dim ToRange As range

    Set FromRange = Application.InputBox("Enter the range from which you want to copy : ", Type:=8)
    FromRange.Select
    Selection.Copy

    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = ActiveWorkbook
    For Each ws In wb.Worksheets
    If ws.Name = "Data_New" Then

            With Worksheets("Data_New")

            If IsEmpty(range("A1")) = False Then
               Cells(1, Columns.count).End(xlToRight).PasteSpecial Paste:=xlPasteValues
            Else
               Cells(1, "A").PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
            End If
            End With

    Else
            Set wst = Worksheets.Add
            wst.Name = "Data_New"

            With Worksheets("Data_New")
            If IsEmpty(range("A1")) = False Then
               Cells(1, Columns.count).End(xlToRight).PasteSpecial Paste:=xlPasteValues
            Else
               wst.Cells(1, "A").PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
            End If
            End With
    End If

    Next

   ' If wst.Cells(1, "A").Value = "" Then
    '    wst.Cells(1, "A").PasteSpecial xlPasteValues, xlPasteSpecialOperationNone

   ' Else
    '     Cells(1, Columns.count).End(xlToRight).PasteSpecial Paste:=xlPasteValues
   ' End If

    'wst.Cells(1, "A").PasteSpecial xlPasteValues, xlPasteSpecialOperationNone

     Cells.Columns.AutoFit    
End Sub
Cocoberry2526
  • 187
  • 3
  • 15
  • 3
    FYI - When you have a `With` statement, you (I assume) want the ranges in that block to apply to the sheet you have in `With Sheet()`? You need to add a point, `.` before `Cells()`, `Range()`, `Rows`, `Columns`, etc. Otherwise, it's going to use the Activesheet to get that information. – BruceWayne Mar 17 '17 at 15:44
  • 1
    Maybe you need to [Test or check if sheet exists](http://stackoverflow.com/q/6688131/4039286) before add new sheet – Fadi Mar 17 '17 at 15:50
  • I went in and added the "." like you said bu that didn't change anything. I keep getting an error on the [wst.Name= "Data_New" line because it says that the sheet is already created. – Cocoberry2526 Mar 17 '17 at 15:51
  • @Fadi thats what I'm trying to figure out how to do, I can't figure out how to write the code to do that – Cocoberry2526 Mar 17 '17 at 15:52
  • @Cocoberry2526, I can't post an answer because this question is marked as duplicate. but instead of looping `For Each` you can use: `On Error Resume Next:Set wst = wb.Worksheets("Data_New"):On Error GoTo 0:If wst Is Nothing Then:Set wst = wb.Worksheets.Add:wst.Name = "Data_New":End If`, replace every `:` with new line. – Fadi Mar 17 '17 at 16:33

0 Answers0